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
     

7 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    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
     
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago
    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's avatar
      Zyg_D
      Continued Contributor
      6 years ago
      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's avatar
        Greg_Deckler
        Community Champion
        6 years ago

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

         

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