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
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
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.
you are welcome