Displaying Average in Grand Total of Power BI Matrix Instead of Sum

[Minseven07]

Hi community!

I'm working with a matrix in Power BI, and I'm looking to display the average instead of the sum in the grand total (PBX file example at the end). I've gone through some posts in the community, but I haven't come across a solution that fits my specific requirement (or perhaps I overlooked it).

what I want to achieve is to have the calculated of the average of the last 10 months in the below highlited column:

In some post, they suggested to change the values:

but this doesn't suit me as it change the whole matrix to AVG

File:

https://drive.google.com/file/d/1th-JftbZrPoTTUNik11bSXEN2l0QxoWM/view?usp=sharing

thx

[Minseven07]

I'm not really a big fan of this approach:

- The average is not what I expected. the average of the sums is what I want to achieve here. example for the group C6 it should be 2.304.

- Moreover, the average should be calculated on the last 10 months. This means for the same group C6 it should be 2.3

- When you change the sorting of the Matrix table, the AVG column is not folowing...

I don't know how this is getting so complicated while easy to achieve on Excel... really. Anyway, thanks for your support.

[amitchandak · Accepted solution]

@Minseven07 , You can use use isincope and switch the total with avg

Assume your have measure is

m1= Sum(Table[ETP])

then you can have measures like

If(isinscope(Date[Month Year]), [M1], Averagex(Values(Date[Month Year]), [M1]) )

How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA

[Minseven07]

thanks! Exactly what I wanted.

[amustafa]

With some formatting and overlaying the copy of the same visual and grouping them worked. See the attached pbix file.

https://1drv.ms/u/s!Aq3n-sopiGyqgolQ4K0eyD9k8RDHXA?e=gz46Vq

[Minseven07]

thanks for your reply but this doesn't really help. the layout is not as expected and their solution is calculating the average. In my case, it is more the average of the sum for each row Group.

[amustafa]

See if this helps:

Solved: How to change total column sum to average in matri... - Microsoft Fabric Community