Forum Discussion
Average of minimum/maximum
RMDFA This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
Greg_Deckler - thanks, I got it to work with your examples. Still not sure exactly why or how it works, but hopefully I can figure it out with your blog.
Community ChampionRMDFA Well, the why and how is basically you are using SUMMARIZE to group based upon the same criteria as you have in your visual is the idea. You add a column either within SUMMARIZE or using ADDCOLUMNS where you calculate your measure for each grouping. Then you use an X aggregator to return the type of aggregation you want like the AVERAGE across all of those summarized values or the minimum or maximum. You could also write it as:
MinOfMeasure =
VAR __Table =
ADDCOLUMNS(
DISTINCT('Table'[Group]),
"__Measure", [Measure]
)
VAR __Result = MINX( __Table, [__Measure] )
RETURN
__Result