Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Rating Matrix Total Average mixed with Rating Count

Hi everybody,

 

this is a problem i could not figure out even with ours of googling.

I have a rating system from 0-10 on 4 different categories.

The visual as matrix works fine after some data transformation (pbix in wetransfer to download included) but the sad part is that i cannot change the total to average, which i need.

 

I tried possible ways to do this with other posts like the AverageX function but could not get it to work so i made some sample data.


Could anyone help me with this one?
Are my steps of transforming the data even unnecessary or is there a better way?

 

I Also want to filter the data regarding each customer as slicer for example. Also included in sample data.

https://we.tl/t-KVwULUbJdv

 

Thank you in advance 😃

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    According to your description, you want to calculate the average of the last column, right? Here are my steps you can follow as a solution.

    (1)We can create  a measure.

     

    Measure = IF(ISINSCOPE(Ratings_Values_Matrix[Category]),AVERAGE(Ratings_Values_Matrix[Count]),SUM(Ratings_Values_Matrix[Count]))

     

    (2) Put [Measure] in the values display.

    (3) Change the subtotal label.

    (4) Then the result is as follows.

    Best Regards,

    Neeko Tang

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Good morning Neeko,

       

      thank you for your help already.

      Sadly  it is not exactly what i need.

      I need the average of the overall ratings in 1 Column.

      For example the first Column. (0x1 + 1x2 + 2x4 + 3x2 + 4x5 + 5x1 + 6x2 + 7x1 + 8x2 + 9x2 + 10x1)

      /(2+4+2+5+1+2+1+2+2+1)    

       

      Is that possible while also having the counts of the certain ratings unchanged?