Comparing to category average

[Zyg_D]

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?

[AlB]

Hi @Zyg_D 

Create a new calculated column in your table:

above_below =
VAR area_avg_ =
    CALCULATE ( AVERAGE ( Table1[grade] ), ALLEXCEPT ( Table1, Table1[area] ) )
RETURN
    SWITCH (
        TRUE (),
        Table1[grade] > area_avg_, "above average",
        Table1[grade] = area_avg_, "average",
        Table1[grade] < area_avg_, "below average"
    )

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[Zyg_D]

---

@AlB wrote:

Create a new calculated column in your table:

above_below =
VAR area_avg_ =
    CALCULATE ( AVERAGE ( Table1[grade] ), ALLEXCEPT ( Table1, Table1[area] ) )
RETURN
    SWITCH (
        TRUE (),
        Table1[grade] > area_avg_, "above average",
        Table1[grade] = area_avg_, "average",
        Table1[grade] < area_avg_, "below average"
    )

---

Although I like that you properly formatted your answer, it evaluated as error to me. 

[camargos88 · Accepted solution]

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:

https://www.daxpatterns.com/dynamic-segmentation/

Did I answer your question? Mark my post as a solution!
Ricardo

[Greg_Deckler]

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")

[Zyg_D]

---

@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.

[Greg_Deckler]

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")

[Zyg_D]

---

@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?