cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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())``````

2 REPLIES 2
Helper I

@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

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 https://community.fabric.microsoft.com/t5/Desktop/Column-subtotal-sum-row-subtotal-average/m-p/95689...

 Vote for my Community Mobile App Idea

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors