Forum Discussion

bwanRPD's avatar
bwanRPD
Frequent Visitor
4 years ago

Average the averages within a Matrix?

Is there a way to have the Matrix visualization average all the rows, but have the grand total average the averages? Please see screenshot below.   I have "elements" which you see all begin w...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi  bwanRPD ,

Here are the steps you can follow：

1. Create measure.

Flag1 =
IF(
   MAX('Table'[Group2])="Cleanliness",
AVERAGEX(FILTER(ALL('Table'),'Table'[Group1]=MAX('Table'[Group1])),[Amount1]),0)
Flag2 =
DIVIDE(
SUMX(ALL('Table'),[Flag1]),
COUNTX(FILTER(ALL('Table'),[Flag1]<>BLANK()),[Flag1]))
VALUE =
IF(
    HASONEVALUE('Table'[Group1]),AVERAGE('Table'[Amount1]),[Flag2])

2. Result:

 

If you need pbix, please click here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

bwanRPD's avatar
bwanRPD
Frequent Visitor
4 years ago

Hi Anonymous 

Thanks for the response! I copied exactly what you had and for some reason not getting the same results...I'm wondering is it because I have more data than what you have in your pbix example? The one that you have in your code is all the scores for quarter 1. In my dataset, there are 4 quarters. Please see below screenshot and also the code that I used that mirrored yours. I would need to be able to separate the calculations based on another criteria that is another column in my table called "Quarter", and possibly also another column called "Year", is that possible? Thanks again for your help.

 

Flag1 = IF(
    MAX('Scores'[Element])="Cleanliness",
    AVERAGEX(FILTER(ALL('Scores'),'Scores'[Feature]=MAX('Scores'[Feature])),[Element_Score]),0)
Flag2 = DIVIDE(
    SUMX(ALL('Scores'),[Flag1]),
    COUNTX(FILTER(ALL('Scores'),[Flag1]<>BLANK()),[Flag1]))
AvgAvg = IF(
    HASONEVALUE('Scores'[Feature]),AVERAGE('Scores'[Element_Score]),[Flag2])