Forum Discussion
Average of minimum/maximum
I have some very simple data (example below), where a day is broken down into hours (0-23), each with some values from another table. I need to create two measures that return the average of the minimum values and the average of the maximum values. It will need to calculate properly at a day/week/etc. level as we roll up.
I see a lot of questions about minimums of averages, but less about averages of minimums. I'm sure it's a simple solution, but I was never able to wrap my head around the "x" functions (minx, sumx, averagex, etc.). Thanks for the help.
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.
3 Replies
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.
RMDFA 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
