使用存储桶的 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屋！