Average of minimum/maximum

[RMDFA]

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.

[Greg_Deckler · Accepted solution]

@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.

[RMDFA]

@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.

[Greg_Deckler]

@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