cancel
Showing results 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())``````

2 REPLIES 2
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

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

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

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

#### 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