SubTotal and GrandTotal isn't summing the average correctly

[JustDavid]

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

[JustDavid]

@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])

[Idrissshatila]

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

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

Follow me on Linkedin
Vote For my Idea 💡