Rating Matrix Total Average mixed with Rating Count

[Anonymous]

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 😃

[v-tangjie-msft]

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]

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?