英文:
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?
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).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论