- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Subtotals average in Matrix - simply average inste...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Ale

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-14-2018
02:21 AM

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.

1 ACCEPTED SOLUTION

Accepted Solutions

v-cherch-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-16-2018
06:34 PM

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

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.

If this post

8 REPLIES 8

Jeltex

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-14-2018
02:33 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-14-2018
02:43 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-14-2018
03:04 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-14-2018
03:51 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-14-2018
04:12 AM

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

Ale

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-14-2018
04:17 AM

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.

v-cherch-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-16-2018
06:34 PM

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

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.

If this post

Ale

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-17-2018
06:20 AM

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.