Forum Discussion
Calculcate Average Over Category in Column
Hi there,
I'm needing to calculate the average value of a column over a category, but in a calculated column form so it is not affected by filters.
I have used the 'Quick Measure' feature to get a similar equation, however, it is affected by filters I have on my visualizations.
Is there any way to do this in a calculated column?
I'm trying to convert a view from TIBCO Spotfire, in which the formula is
Avg([Numer]) OVER ([Category]) * 7.25 * 10 * 26
(Please excuse the dummy variable names).
Thank you
Hi Anonymous
Can you try the creating the following:
Measure =
VAR _1 = 7.25 * 10 * 26
RETURN
CALCULATE ( AVERAGE ( Table[Amount] ) , ALLEXCEPT ( Table , Table[Category] ) ) * _1Just adjust the Table to your Table Name; Amount to the amount column name; and the Category to the name of your Category column.
Hope this helps!
Theo 🙂
2 Replies
- TheoC
Community Champion
Hi Anonymous
Can you try the creating the following:
Measure =
VAR _1 = 7.25 * 10 * 26
RETURN
CALCULATE ( AVERAGE ( Table[Amount] ) , ALLEXCEPT ( Table , Table[Category] ) ) * _1Just adjust the Table to your Table Name; Amount to the amount column name; and the Category to the name of your Category column.
Hope this helps!
Theo 🙂