Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

SubTotal and GrandTotal isn't summing the average correctly

PowerBI experts,


I have the following simple matrix table. I have filter it down to 1 single MPC value for simplicity sake.


As you can see, the [Avg. 5YP] on the individual row (highlighted in green) is calculated correctly. However, the subtotal and grand total isn't. What I'd like to accomplish here is to get the average of subtotal and grandtotal to be the SUM of the (green colored) averages divide by the count of ProdType.


Am wondering if it's doable where a measure would shows different "average" values.


In a sense, if it's on MPC subtotal (in this case HMW), it'll sum the averages of all of the level of detail (shown in green) and divide by the distinct count of 


Below are my measures:


DistinctCount of Models = 
   DISTINCTCOUNT('CombineProformaHA-and-5YP'[Model Code])

5YP-HA = 
   SUM('CombineProformaHA-and-5YP'[5YP Cost])

Avg. 5YP = 
   DIVIDE([5YP-HA], [DistinctCount of Models], BLANK())



SUM Measures Averages.png

Frequent Visitor

@Idrissshatila Thank you for directing me to the post. I was stuck with it and finally able to get it work.

Below is my solution.

I created a column "[ComID-Phase-ProdType-ID]" that concatenate 'comID', 'Phase' and 'ID' and using this, I was able to use the VALUES. Tried doing it without creating this concatenate column, and seems like it didn't work (I'm  a beginner with PowerBI).

Weighted Avg. 5YP = AVERAGEX(
    VALUES('Combine5YP'[ComID-Phase-ProdType-ID]), [Avg. 5YP]

Avg. 5YP = DIVIDE([5YP-HA], [DistinctCount of Models])

5YP HA Cost = SUM('Combine5YP'[5YP Cost])


Super User
Super User

Hello @JustDavid ,


so the thing with matrix is that it doesn't sum the average in the subtotal and grand total but it gives an average of them.


But check this so;ution where they have the same case as yours


If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea 💡

Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors