Forum Discussion
Maximums and minimums
aidaamieira 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.
Hi Greg_Deckler
Thank you very much for your reply.
Yes, this is indeed an aggregation problem.
I've looked at your article and the formulas you've provided, but I need something that is dynamic and changes depending on the quarter selected in the filter.
In other words, in the case of Q4 (Cumulative) 2022, the table should only calculate the average values using Q1 2022, Q2 2022, Q3 2022 and Q4 2022, but for Q1 (Cumulative) 2023, the average values should be calculated using Q1 2023, Q2 2022, Q3 2022 and Q4 2022, and so on.
From the solution you left me, it seems to me that the tables are static and for my case I would have to have as many tables as I have accumulated quarters, a situation I am trying to avoid since the base is always being updated. Can you help with any other solutions?
Thank you in advance for your attention and availability.
aidaamieira You could certainly use a table variable in those formulas. For example:
Measure = VAR __Date = MAX('Table'[Date]) VAR __MinDate = __Date - 365 VAR __Table = FILTER('Table', [Date] >= __MinDate && <= __Date) VAR __Result = AVERAGEX ( SUMMARIZE ( __Table, [Group] , "Measure",[YourMeasure] ), [Measure]) RETURN __Result