Create Measure to Return Highest Value

[Lucy01]

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, Average, and Below Average so I would like to create a measure that returns the highest of the three. My approach is to do this one at a time, so one measure that returns Above Average, one for Average, and one for Below Average.

I’ve tried to use the below DAX but it results in an error message:

Comparison operations do not support in comparing values of type TRUE/FALSE with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values

Measure Name =

   IF(

      'Sheet 1'[Attendance Above Average] > 'Sheet 1'[Attendance Average] = "Attendance Above Average",

      'Sheet 1'[Attendance Above Average] > 'Sheet 1'[Attendance Below Average = "Attendance Above Average")

The results are recorded as General on the source spreadsheet as it is not a number. Do I need to convert the source spreadsheet to a number in order for the DAX to work, or is there a simpler solution to returning the highest result?

Thank you

[Jihwan_Kim]

Hi, I am not sure how your exptected outcome looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attahced pbix file.

[FBergamaschi]

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]

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]

@Lucy01 

could you pls provide some sample data and expected output?

[Lucy01]

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

[ryan_mayu · Accepted solution]

@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 

[Lucy01]

I tried again and the second option worked as a solution. I must have been mistyping it originally. Thank you very much for all your support on this.

[ryan_mayu]

you are welcome

[Lucy01]

Thank you, the averages are all measures. Would I be better off using columns?

Please find sample data attached, thank you

Sample Data 

[ryan_mayu]

@Lucy01 

I don't have the access to it. could you pls grant the access?