DAX-总计-多个Critiera +分组 [英] DAX - Running Total - Multiple Critiera + Grouping

问题描述

我到处都在寻找这个，但是其他任何例子都不是我想要实现的。我的数据看起来像这样：

I've looked everywhere for this but any other example is not exactly what I want to achieve. I have data that looks like that:

Day     Group        Sales
14        1            15
13        0            22
14        1            17

，依此类推。现在，我使用以下公式创建运行总计的计算字段：

and so on. Now I create calculated field of Running Total using following formula:

CALCULATE (
    SUM(Table[Sales]),
        FILTER (
            ALL (table),
            Table[Date] <= MAX(Table[Date])
         )
    )

我创建数据透视表，然后将日期和组作为行，然后将总计作为值。为什么未对每个组分别汇总运行总计？我希望第1组进行计算，第0组也是如此。

I create Pivot Table and put Date and Group as rows, then Running Total as Values. Why running total is not aggregated separately for each group? I would like Group 1 to have its calculation, and Group 0 too.

推荐答案

之所以没有为每个组分别汇总运行总计是因为您的公式不完整。您刚刚将ALL函数用于FILTER。 ALL将意味着您将忽略现有的过滤器上下文。因此，在具有组0的行上，您将忽略组0过滤器。

The reason that running total is not aggregated separately for each group is because your formula is incomplete. You have just used the ALL function for FILTER. The ALL will mean that you will ignore the existing filter contexts. So on a row with Group 0 you will ignore the Group 0 filter.

我想您想要保留的是组过滤器。您可以通过将 ALL（表），替换为 ALLEXCEPT（table，table [Group]），或 ALL（table [Date]），。这两个都将为组行提供总计，因此您将获得组0的运行总计，组1的运行总计。

What I think you want is to retain the Group filter. You can achieve this by replacing ALL (table), with either ALLEXCEPT(table,table[Group]), or ALL(table[Date]),. Both of these will give a running total just for the Group row so you will have a running total for Group 0 and one for Group 1.

是一个运行总计，其中可以按日期和组进行总计，您可以保留ALL并为FILTER添加一个额外的表达式：

If what you are looking for is a running total where the totals accumulate by both Date and Group you can keep ALL and add an extra expression for FILTER:

CALCULATE (
    SUM ( table[Sales] ),
    FILTER (
        ALL ( table ),
        table[Date] <= MAX ( table[Date] )
            && table[Group] <= MAX ( table[Group] )
    )
)

这篇关于DAX-总计-多个Critiera +分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！