Calculcate Average Over Category in Column

[Anonymous]

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

[TheoC · Accepted solution]

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] ) ) * _1

Just 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 🙂

[Anonymous]

Thank you for your repsonse.