Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My dataset is structured in a way where I have 3 location columns and their respective values:
Global | Region | Country | Value | Trend
All Sites | Emerging Markets| Japan | 700 | 0
All Sites | Europe Sites | China | 1500 | 1
All Sites | Japan | Germany | 800 | 0
...
So, I built a Matrix that displays this values for each country, but I also want to show the average for each region and for global. Therefore, in my matrix rows I used all 3 columns Global / Region / Country, and for columns I used Value and Trend. I use row subtotals to get the values for Region and Global. The formula to calculate the values for each row is simply: AVERAGE([Value]). In this case, the subtotal will be already an average of the values below it.
For the Region subtotal, the result is fine, as you can see in the image below. However, for the Global one, the result should be a simple average of the Regions, and not an average of all Countries. It sounds wrong, but this is what the stakeholders want.
As you see, if you sum up the value of the 3 regions (93 + 88.2 + 92.5) and calculate the average, it should be 91,2 instead of 90,4. I can't find a way to get this result. Does anyone have an idea?
Thank you so much!
Solved! Go to Solution.
Hi @Ale
You may try to use below measure. Show the simplified sample as below:
Measure = SUMX ( SUMMARIZE ( Table1, Table1[Region], "a", AVERAGE ( Table1[Value] ) ), [a] ) / DISTINCTCOUNT ( Table1[Region] )
Regards,
Cherie
Hi @Ale,
I personally avoid the AVERAGE() function because in moest of the times does not deliver the result that I expect to get.
This measure should point you in the right direction:
AVG = SUM(Value)/DISTINCTCOUNT(Region)
You might have to put a filter context around the distinctcount.
Hi, thanks for the answer, but this does not work.
The problem is: if I use DISTINCTCOUNT(Region), my subtotal for region gets the wrong value (it sums everything and then divide by 1). If I use DISTINCTCOUNT(Country), then the subtotal for the region is correct but the Global continues with the same problem.
What I would need is exactly 2 different things here.
For Region subtotal: sum up all countries and divide by number of countries;
For Global subtotal: sum up all regions and divide by number of regions;
Ah I read to fast.
Instead of region does it help if you pick the values that you have masked out?
And then check both COUNT and DISTINCTCOUNT.
Not sure what you meant by values I masked out.
The problem is that I need to do the average based on the lower level for each column, but PowerBI always do the average based on the Lowest level.
What PowerBI is doing:
Region -> Average of region's countries
Global -> Average of world's countries
What I need:
Region -> Average of region's countries
Global -> Average of regions
I meant the lowest level. So try using COUNT(Country) instead of region
Oh, that's what I tried the first time with the formula you suggested.
I tried with both DISTINCTCOUNT(Region) and DISTINCTCOUNT(Country).
DISTINCTCOUNT(Region) -> goes wrong because it sums up the countries and divide by 1 Region, so I get high numbers like 530 or something.
DISTINCTCOUNT(Country) -> goes wrong because for the Global level, it will keep averaging the countries, but in this case I want to average the regions.
Hi @Ale
You may try to use below measure. Show the simplified sample as below:
Measure = SUMX ( SUMMARIZE ( Table1, Table1[Region], "a", AVERAGE ( Table1[Value] ) ), [a] ) / DISTINCTCOUNT ( Table1[Region] )
Regards,
Cherie
Thank you so much, Cherie!
On Friday I found another way to make it work but I had to create one measure for each region, which of course is not optimal, since whenever a new region is added, I would have to create a new measure and add it to the formula.
But your solution is very simple and works perfectly.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |