Average the averages within a Matrix?

[bwanRPD]

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 with "Cleanliness", all of those elements are averaged up to the feature, which are the 4 groups: Buildings & General Amenities, Hardscape, Ornamental Beds, and Trees. I then have the grand total at the bottom, which does what the Matrix usually does and averages all the records associated which becomes 0.92.

What I want to do instead is have the grand total average all of the 4 different feature groups, which is: 0.86, 0.88, 1.00, and 1.00 which would average to 0.935 instead of 0.92.

[Anonymous]

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]

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

[amitchandak]

@bwanRPD , Using the columns in the matrix

Averagex(Addcolumns(Summarize(Table, Table[Buliding], Table[Element]), "_1", [Avg]), [_1])

[bwanRPD]

Sorry, one more question, if your DAX, what is [Avg]? is that a different table that you are referencing?

[bwanRPD]

Hi, sorry I am not as familiar with writing DAX in PBI. Am I adding this in as a measure within the Matrix, or a separate column in the data table?

The DAX you provided gives a couple tables in the parameter, but the "Building" and "Element" is all part of the same 1 table.