cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ale Regular Visitor
Regular Visitor

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

Accepted Solutions
Community Support Team
Community Support Team

Re: Subtotals average in Matrix - simply average instead of weighted

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.
8 REPLIES 8
Jeltex Member
Member

Re: Subtotals average in Matrix - simply average instead of weighted

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.

Ale Regular Visitor
Regular Visitor

Re: Subtotals average in Matrix - simply average instead of weighted

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;

Jeltex Member
Member

Re: Subtotals average in Matrix - simply average instead of weighted

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.

Ale Regular Visitor
Regular Visitor

Re: Subtotals average in Matrix - simply average instead of weighted

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

Jeltex Member
Member

Re: Subtotals average in Matrix - simply average instead of weighted

I meant the lowest level. So try using COUNT(Country)  instead of region

Ale Regular Visitor
Regular Visitor

Re: Subtotals average in Matrix - simply average instead of weighted

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.

Community Support Team
Community Support Team

Re: Subtotals average in Matrix - simply average instead of weighted

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.
Ale Regular Visitor
Regular Visitor

Re: Subtotals average in Matrix - simply average instead of weighted

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.