Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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.
@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
thanks! Exactly what I wanted.
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
Proud to be a Super User!
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.
See if this helps:
Solved: How to change total column sum to average in matri... - Microsoft Fabric Community
Proud to be a Super User!
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |