Forum Discussion
Solved
Comparing to category average
I have the 3 first columns, and I want to get the blue one. The values are obtained by comparing grades to grade averages of each area. How to do it?
Hi Zyg_D ,
Try this one:
Column =VAR _avg = CALCULATE(AVERAGE('Table (2)'[Grade]); FILTER('Table (2)'; 'Table (2)'[Area] = EARLIER('Table (2)'[Area])))RETURN IF('Table (2)'[Grade] < _avg; "Below Average";IF('Table (2)'[Grade] = _avg; "Average";"Above Average"))This column is static, if you need it dynamically you can use this link as reference:Did I answer your question? Mark my post as a solution!
Ricardo
above/below =
VAR __grade = MAX('Table'[grade])
VAR __area = MAX('Table'[area])
VAR __average = AVERAGEX(FILTER(ALL('Table'),[area]=__area),[grade])
IF(__grade >= __average,"above average","below average")
VAR __grade = MAX('Table'[grade])
VAR __area = MAX('Table'[area])
VAR __average = AVERAGEX(FILTER(ALL('Table'),[area]=__area),[grade])
IF(__grade >= __average,"above average","below average")
Greg_Deckler wrote:
above/below =
VAR __grade = MAX('Table'[grade])
VAR __area = MAX('Table'[area])
VAR __average = AVERAGEX(FILTER(ALL('Table'),[area]=__area),[grade])
IF(__grade >= __average,"above average","below average")Sorry, even after adding RETURN before IF it yields wrong results - always above average.
Because it is a measure and you are probably trying to use it in a column.
Column form:
above/below = VAR __grade = [grade] VAR __area = [area] VAR __average = AVERAGEX(FILTER('Table',[area]=__area),[grade]) RETURN IF(__grade >= __average,"above average","below average")
Greg_Deckler wrote:Because it is a measure and you are probably trying to use it in a column.
Column form:
above/below = VAR __grade = [grade] VAR __area = [area] VAR __average = AVERAGEX(FILTER('Table',[area]=__area),[grade]) RETURN IF(__grade >= __average,"above average","below average")Is performance-wise measure better than calculated column?
