DAX带桶总计 [英] DAX Running Total with Buckets

问题描述

我刚接触Power BI / DAX，但无法以自己所需的方式来运行总费用。假设下表包含数据：

I'm newish to Power BI/DAX, and I'm having trouble getting a running total to work the way I need. Assume the following table for data:

User    month   sales
UserA   1/1/2019    1
UserB   1/1/2019    3
UserC   1/1/2019    2
UserA   2/1/2019    1
UserB   2/1/2019    3
UserC   2/1/2019    2
UserA   3/1/2019    1
UserB   3/1/2019    3
UserC   3/1/2019    2

我一直在环顾四周，我发现以下公式为我提供了所需的良好运行总计：

I've been looking around and I've found the following formula gives me a good running total the way I need:

AllSales = 
calculate(
    sum('table'[Sales]),
    filter(
        all ('table'),
        'table'[date] <= max ('table'[date])
        )
)

-

Total   6   12  18  18

问题来了，当我想以矩阵形式查看此信息时，用户会分成多个存储桶。当我这样做时，每个用户的销售数量是相同的：

The problem comes when I want to see this in matrix form with the users breaking out into buckets. When I do this, the number of sales is the same for each user:

UserA   6   12  18  18
UserB   6   12  18  18
UserC   6   12  18  18
Total   6   12  18  18

我想要的结果如下所示：

My desired outcome would look like this:

UserA   1   2   3   3
UserB   3   6   9   9
UserC   2   4   6   6
Total   6   12  18  18

我相信我理解为什么ALL函数导致了此问题，但是我不知道如何调整它或切换到哪个函数以解决此问题。任何帮助将不胜感激。谢谢！

I believe I understand why the ALL function is causing this, but I don't know how to tweak it or which function to switch to in order to resolve this issue. Any help would be very much appreciated. Thanks!

推荐答案

不是将ALL应用于整个表，而是仅将其应用于所需的列：

Instead of applying ALL to the entire table, apply it only to the column you need:

AllSales =
CALCULATE (
    SUM ( 'table'[Sales] ),
    FILTER ( ALL ( 'table'[date] ), 'table'[date] <= MAX ( 'table'[date] ) )
)

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