2组中的DAX运行总计（或计数） [英] DAX running total (or count) across 2 groups

问题描述

我对DAX和PowerPivots非常陌生。我正在使用PowerPivot从SQL Server中提取数据。我正在使用的数据非常庞大和复杂，但我正在考虑使用此问题的简化版本。

I'm very new to DAX and PowerPivots. I am using PowerPivot to pull data from an SQL server. The data I'm working with is pretty large and complex but I'm considering a simplified version of it for this question.

假设我有2列，其中一列包含产品名称，一个包含产品销售日期。

Let's say I have 2 columns, one that contains product names and one that contains a date that product was sold. I have a row for each time 1 unit of any product is sold.

Product | Date
Orange  | 08/13/2013
Orange  | 08/13/2013
Orange  | 08/13/2013
Apple   | 08/14/2013
Apple   | 08/16/2013
Orange  | 08/17/2013
Orange  | 08/17/2013

我想使用DAX来获得产品数量的连续计数迄今为止已在整个数据集中出售。这就是我最终想要得到的。

I want to use DAX to get a running count of how much of a product has been sold to date over the entire data set. This is what I would like to end up with.

Product | Date        | Cumulative Sales
Orange  | 08/13/2013  | 1
Orange  | 08/13/2013  | 2
Orange  | 08/13/2013  | 3
Apple   | 08/14/2013  | 1
Apple   | 08/16/2013  | 2
Orange  | 08/17/2013  | 4
Orange  | 08/17/2013  | 5

任何帮助将不胜感激。

编辑：另外，数据不一定按日期排序。我可以按日期排序，但可能需要修改其他一些内容，因此，如果可能的话，我不希望这样做。我继承的工作表中还有很多其他公式，重新排序可能会破坏其他内容。

One more thing, the data is not necessarily ordered by date. I could potentially order it by date but it would require modification of some other things so my preference would be not to do so if at all possible. There are a lot of other formulas in the sheet I inherited and reordering may break something else.

推荐答案

您可以进行计算得出的量度在PowerPivot中处理此问题。如果您想获得所有时间的累计销售额，可以执行以下操作：

You can make a calculated measure in PowerPivot to handle this. If you want to get the cumulative sales for all time you can do this:

    CALCULATE(     SUM( FactSales[SalesAmount] ),
    FILTER(
        ALL( DimDate) ,
        DimDate[Datekey] <= MAX( DimDate[Datekey] )
    )
)

如果您希望能够选择某些时间段（例如：选定几周或几个月的累计运行时间），可以执行以下操作： / p>

If you want to be able to select certain time period (ex: running total for selected weeks or months) you can do this:

 CALCULATE( SUM( FactSales[SalesAmount])  ,
                 FILTER(
                    ALLSELECTED( DimDate),
                    DimDate[Datekey] <= MAX( DimDate[Datekey] )
                )
    )

来源：哈维尔·吉伦的博客

这篇关于2组中的DAX运行总计（或计数）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！