Power BI DAX:获取基于另一列的总和 [英] Power BI DAX : Get sum of a column based on another

查看:87
本文介绍了Power BI DAX:获取基于另一列的总和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

让我说一下下面的桌子:

Let say I have the folowwing table :

Client    Sales
A         1000
A         100
A         10
B         1000
B         10
C         1

我想在末尾添加另一个名为TotalClient的列,新表将如下所示:

I would like to add another column Names TotalClient at the end which would the new table look like this :

Client    Sales   Total
A         1000    1110
A         100     1110
A         10      1110
B         1000    1010
B         10      1010
C         1       1

在DAX或查询编辑器中是否可能?我尝试了很多方法,但仍然不知道怎么做.

Is this possible either in DAX or in the query editor ? I tried many ways, but still can't figure out how.

我能够使用SUMMARIZE函数获取所需的信息以获取另一个表,但是在安全性方面遇到了其他问题.似乎可以正确过滤数据表,但不能过滤汇总表.

I was able to get information I needed using the SUMMARIZE function to get another table, but I run into other problem in term of security. It seems the data table gets filtered right, but not the summarized version.

谢谢

推荐答案

您只需要知道如何使用 CALCULATE 来调整过滤器上下文.

You just need to know how to use CALCULATE to adjust your filter context.

Total = CALCULATE( SUM( Table1[Sales] ), ALLEXCEPT( Table1, Table1[Client] ) )

这表示要计算表中所有行的销售额的总和,在该表中,我们删除了除客户端之外的任何行上下文.这样,您将获得客户端与当前行中的客户端匹配的所有行的总和.

This says to calculate the sum of the sales for all rows in the table where we've removed any row context except for the client. Thus you get the sum over all rows where the client matches the client in the current row.

如果只有这些列,则可以这样做

If you only have these columns, you can do this

Total = CALCULATE( SUM( Table1[Sales] ), ALL( Table1[Sales] ) )

这仅删除 Sales 行上下文,并保留 Client 行上下文.如果您还有其他列,则可能无法按预期方式工作,因为它们仍将是行上下文的一部分.

This removes only the Sales row context and leaves the Client row context. This probably will not work as expected if you have other columns as well since they will still be part of the row context.

您还可以使用 ALL 删除所有过滤器上下文,然后在所需的过滤器中明确添加回去:

You can also remove all filter context with ALL and then explicitly add back in the filtering you want:

Total =
CALCULATE(
    SUM( Table1[Sales] ),
    ALL( Table1 ),
    Table1[Client] = EARLIER( Table1[Client] )
)

或类似地代替 SUMX

Total =
SUMX(
    FILTER(
        Table1,
        Table1[Client] = EARLIER( Table1[Client] )
    ),
    Table1[Sales]
)


许多种给猫皮的方法.


Lots of ways to skin the cat.

这篇关于Power BI DAX:获取基于另一列的总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆