Forum Discussion

Lucy01's avatar
Lucy01
Helper I
7 months ago
Solved

Create Measure to Return Highest Value

I’m trying to create a measure that returns the highest result out of three options.   For example, I want to create a trendline for student attendance and the three options are Above Average, Aver...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    7 months ago

    Lucy01 

    you can try this

     

    Classwork Max Result =
    CALCULATE(
    MAX('Sheet1'[Value]),
    ALLEXCEPT('Sheet1', 'Sheet1'[Month])
    )

     

    Classwork Max Category =
    VAR tbl =
    ADDCOLUMNS(
    {
    ( "Above Average", [Classwork Above Average] ),
    ( "Average", [Classwork Average] ),
    ( "Below Average", [Classwork Below Average] )
    },
    "Category", [Value]
    )
    VAR MaxVal = MAXX(tbl, [Value])
    RETURN
    SELECTCOLUMNS(
    FILTER(tbl, [Value] = MaxVal),
    "Category", [Category]
    )

     

    Are these three average data measures or columns? It's better to provide some sample data 

FBergamaschi's avatar
FBergamaschi
Super User
7 months ago

Hi Lucy01,

Try the below

 

Measure Name =

   MAX ( [Attendance Above Average], MAX ( Attendance Average, Attendance Below Average ) )

 

I am assuming [Attendance Above Average] and so on indicate a measureso I dropped the table name.

 

Best

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

  • Lucy01's avatar
    Lucy01
    Helper I
    7 months ago

    Hello,

     

    Thanks for your help. That very nearly works but for some reason when I put the measures into a chart, it returns the same value for each. I've checked it against my source spreadsheet and the results should definitely vary.

     

    I've tried to figure out why it's returning the same values, but I'm not sure. Any help would be gratefully received.

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      7 months ago

      Lucy01 

      could you pls provide some sample data and expected output?

      • Lucy01's avatar
        Lucy01
        Helper I
        7 months ago

        Here's an image of what my current model looks like. The top chart is Classwork by Month and as you can see from the tooltips, the results vary each month.

        In the 2nd chart I’ve used

        Classwork Above Average Max = MAX('Sheet1'[Classwork Above Average], MAX([Classwork Average], [Classwork Below Average]) )

        to create measures for each result (Above Average, Average, Below Average) but each measure returns the same result. You can see April in both charts for comparison.

        Ideally, what I’d like to create is a trendline that compares results monthly and returns the highest result for that month. For example, if in June the highest score for all Classwork is Average, it would return Average on the chart.

         

        I appreciate any support you can provide. Thank you