DAX – 在Power BI中,将每个分组的总和除以所有分组的总和

huangapple go评论99阅读模式
英文:

DAX - divide sum of each group by the total sum of all groups in Power BI

问题

我有如下表格,如截图所示。我有一个名为"Together"的度量,它对每个城市的"APP"和"POS"值求和。
现在我需要将"Together"列下每个城市的总和除以相同列下所有城市的总和。

所以对于第一个城市,我应该有272/3800 = 0.0715
第二个城市,271/3800 = 0.0713
.
.
.
依此类推。

如何使用DAX计算这个值?

我尝试过使用Divide(Together, Sum(Together)),但它返回1。

英文:

I have below table as shown in the screenshot. I have a measure called Together which sums the APP and POS value for each city.
Now i need to divide the sum value of each city under the Together column over the sum of all cities under same column.

so for the first city i should have 272/3800 = 0.0715
second city, 271/3800 = 0.0713
.
.
.
And so on.

How do i calculate this using DAX?

Screenshot

What i tried is Divide(Together, Sum(Together)) but it gives 1.

答案1

得分: 0

Step 1: Together measure

Together = SUMX('Cities',[App]+[Pos])

Step 2: AllCities measure

AllCities = CALCULATE([Together],ALL('Cities'[City]))

Step 3: Share measure

Share = DIVIDE([Together],[AllCities],BLANK())
英文:

Step 1: Together measure

Together = SUMX('Cities',[App]+[Pos])

Step 2: AllCities measure

AllCities = CALCULATE([Together],ALL('Cities'[City]))

Step 3: Share measure

Share = DIVIDE([Together],[AllCities],BLANK())

Looks like you're coming from Excel, because the logic you outlined is normal Excel logic. If you think in terms of a database instead, then the logic may be easier. ALL() and REMOVEFILTERS() remove some or all parts of a filter context (In AllCities measure, I'm removing the [City] part of the filter context, so that I get the total for all cities. Then I Divide in Share. (Sure, I could do it in one measure, but I find it easier to understand if I do it a piece at a time).

huangapple
  • 本文由 发表于 2023年8月4日 05:00:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/76831574.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定