Forum Discussion
Helper IIWrong answer ... going crazy ... must be simple?
sps-reporter Yes, you can use a measure in SUMMARIZE. 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.
Community Championsps-reporter Yes, you can use a measure in SUMMARIZE. 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.
Helper IIGreg_DecklerThat was it!!! Thank you! Now I just have to figure out why it works so I don't paint myself into this corner again. My DAX experience is wobbly but this will help me advance.
- Greg_Deckler
sps-reporter Well, the Measure Aggregation and Measures Total problems are kind of related. In fact, the second came from the first as I recall. Basically what it sounds like you are running into boils down to context. So, to put this in measures totals land which is easier to explain, when you are in the total line of table visualization, you are in the context of ALL. So your measure gets evaluated in this context and probably more often than not the total line is "wrong" because it is not an aggregation at the line level. So, by using a measure that works at the line level and then using something like SUMMARIZE or GROUPBY to recreate that line level context within the measure you can then use an "X" aggregation function across that table to get the "correct" aggregation at the total level. Now, when it comes to measure aggregation, understand that you have this same kind of issue within a hierarchy, not just at a total line.