Forum Discussion
Calculate Average per category
Hello everyone,
I spent a lot of time already looking at similar problems to the one I have, but nothing really worked for me. Assume I have a table with a category and some values.
How can I calculate a measure or a new column that shows the average of the values for one category like in my example below?
category | value | average
----------------------------
A | 2 | 1.5
A | 1 | 1.5
B | 3 | 2
B | 2 | 2
B | 1 | 2
I already tried:
AverageMeasure= CALCULATE(AVERAGE(table[value]); FILTER(table; table[category]=EARLIER(table[category]))
but it gives me an error saying that EARLIER refers to an earlier row context that does not exist. And when I put an explicit category in the FILTER statement, the result seems to be just the original value per row.
Any ideas?
Hi mschultens,
Did you use the original expression in the calculated column? It works for me. Please refer:
Or you can try measure:
AverageMeasure = CALCULATE ( AVERAGE ( Table3[Value] ), FILTER ( ALLSELECTED ( Table3 ), Table3[Category] = MAX ( Table3[Category] ) ) )Thanks,
Xi Jin.
11 Replies
HI mschultens
Try this MEASURE.
Earlier typically works in a Column not a MEASURE
AverageMeasure = CALCULATE ( AVERAGE ( table[value] ), ALLEXCEPT ( Table, table[category] ) )
You can simply use Average(table[value]) as well if you choose not to put VALUE in the Table VISUAL
Your formula would work as a calculated column
Thank you, but that does not work for me, because I want to put the different averages into one visualization.
Thank you! That just helped me with a similar issue too.
