让我说一下下面的桌子:
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:获取基于另一列的总和
发布评论