Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ale
Resolver II
Resolver II

Subtotals average in Matrix - simply average instead of weighted

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!

1 ACCEPTED 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] )

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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;

Anonymous
Not applicable

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

Anonymous
Not applicable

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] )

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.