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

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    bwanRPD , Using the columns in the matrix

     

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

    • bwanRPD's avatar
      bwanRPD
      Frequent Visitor
      4 years ago

      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.

    • bwanRPD's avatar
      bwanRPD
      Frequent Visitor
      4 years ago

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

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