Maximums and minimums

[aidaamieira]

Hi,

I have a database with quarterly results. I want to present the average values of the quarterly results, but also the results of the aggregated quarters, i.e. the dashboard has the results of:
1Q 2021;
1Q Accumulated 2021 (1Q 2021);
2Q 2021;
Accumulated 2Q 2021 (1Q 2021 + 2Q 2021)
3Q 2021;
Accumulated 3Q 2021 (1Q 2021 + 2Q 2021 + 3Q 2021)
4Q 2021;
4Q Accumulated 2021 (1Q 2021 + 2Q 2021 + 3Q 2021 + 4Q 2021)
1Q 2022;
Accumulated 1Q 2022 (2Q 2021 + 3Q 2021 + 4Q 2021 + 1Q2022);
2Q 2022;
Accumulated 2Q 2022 (3Q 2021 + 4Q 2021 + 1Q2022 + 2Q2022);
3Q 2022;
Accumulated 3Q 2022 (4Q 2021 + 1Q2022 + 2Q2022 + 3Q2022);
4Q 2022;
4Q Accumulated 2022 (1Q2022 + 2Q2022 + 3Q2022 + 4Q2022);

The filters to be placed on the slide have the following options:
Year: 2021 and 2022
Quarter: 1Q, 1Q (Cumulative), 2Q, 2Q (Cumulative), 3Q, 3Q (Cumulative), 4Q and 4Q (Cumulative)
Company: Company A, Company B, Company C, ...
Sector: Sector A, Sector B, ...

All filters except the company are single-select.
In order to associate the accumulated quarter with the year, I imported a table containing the following fields:
Year: 2021, 2022
Cumulative_Quarter: 1Q, 1Q (Cumulative), 2Q, 2Q (Cumulative), 3Q, 3Q (Cumulative), 4Q and 4Q(Cumulative)
Cumulative_Year: 2021, 2022
Quarter: 1Q, 2Q, 3Q and 4Q
Date: corresponds to the date on which each of the quarters begins.
For better interpretation:

This table is linked to a table containing dates by quarter and which is linked to the database, always by date.

My problem is that I need to display the maximum and minimum values of the average value.
In other words, let's say I have the following average values per company for the Final Grade variable:

Company A: 7.4
Company B: 7.5
Company C: 6.2
Company 😧 8.4

In the graph I want to display, I have to have the average value of the selected company, but at the same time the maximum and minimum value between companies A, B, C and D.

The average value is a metric and is being calculated as follows:
DIVIDE(CALCULATE(SUM('Bases Trimestrais'[valor_fe]),'Bases Trimestrais'[Value]>=0 && 'Bases Trimestrais'[Value]<11),CALCULATE(SUM('Bases Trimestrais'[FE]),'Bases Trimestrais'[Value]>=0 && 'Bases Trimestrais'[Value]<11))

Since the only way I could find to return the maximum or minimum was if these values were in a column, I created the following table:
indicador_agreg = SUMMARIZE('Bases Trimestrais','Bases Trimestrais'[ID_ValoresMédios],'Bases Trimestrais'[date],"valor_medio",
divide( calculate(
sum('Bases Trimestrais'[valor_fe]),

and('Bases Trimestrais'[Value]>0,'Bases Trimestrais'[Value]<10.1))
,calculate(
sum('Bases Trimestrais'[FE]),

and('Bases Trimestrais'[Value]>0,'Bases Trimestrais'[Value]<10.1))))

I then calculate the maximum and minimum using the following formulae:
Valor Max = calculate(maxx(indicador_agreg,indicador_agreg[valor_medio]),all(base_total[Empresa]))
Valor Min = calculate(minx(indicador_agreg,indicador_agreg[valor_medio]),all(base_total[Empresa]))

For the single quarters, the maximum and minimum values are correct, the problem is when we move on to the accumulated. It would be necessary to create a table just like the indicator_agreg but which would change according to the quarter that was selected.

Does anyone know how to do this?
Thank you in advance for your reply

[Greg_Deckler]

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

[aidaamieira]

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.

[Greg_Deckler]

@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