Forum Discussion
Create Measure to Return Highest Value
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
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.
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
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
Thank you, the averages are all measures. Would I be better off using columns?
Please find sample data attached, thank you