Forum Discussion

Zyg_D's avatar
Zyg_D
Continued Contributor
6 years ago
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?
Need Help
Show and Tell
  • camargos88's avatar
    camargos88
    6 years ago

    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
     
AlB's avatar
AlB
Community Champion
6 years ago

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's avatar
Zyg_D
Continued Contributor
6 years ago
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.