How to get the max value of quarterly sum with a measure?

[Anonymous]

I have the below data. 

Fiscal Year	Fiscal Qtr	qtd	Total QTD	Max QTD
2020	Q1	10	100	40
2020	Q2	20	100	40
2020	Q3	30	100	40
2020	Q4	40	100	40
2021	Q1	5	80	35
2021	Q2	15	80	35
2021	Q3	25	80	35
2021	Q4	35	80	35

I am trying to achieve the last two columns in the above table with two measures.

The qtd field is the raw data. I got the [Total QTD] to populate correctly with the measure.

[Total QTD] = CALCULATE(SUM('Table'[qtd]),ALL('Date'),VALUES('Date'[Fiscal Year]))

This shows the sum of qtd value for the year, for all quarters.

Similarly, I am trying to get the Max of qtd, by replacing the sum with 'max' in the same formula but it doesn't work. 

Can someone please tell me how to get this?

Thank you very much in advance.

[CNENFRNL]

@Anonymous , you may want to try measures,

Total QTD = CALCULATE ( SUM ( Sales[qtd] ), ALLEXCEPT ( Sales, Sales[Fiscal Year] ) )

Max QTD = 
MAXX (
    CALCULATETABLE ( Sales, ALLEXCEPT ( Sales, Sales[Fiscal Year] ) ),
    Sales[qtd]
)

[Anonymous]

@CNENFRNL Thank you for your response.

I tried your Max QTD measure but it's not returning the right values for me.

I need the Max QTD to get poulated with 2020 Q4 value = 40 for all 2020.

Please notice that the 40 is a sum() several different values, for example 20+10+10. The measure is populating Max QTD with 20. Beacuse 20 Is the max value for that quarter.

But I want it to take the sum of 2020 Q4 which is 40. and show it in the Max QTD.

Could you please help me acieve this?

Thanks in advance.

[Greg_Deckler · Accepted solution]

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

[Anonymous]

@Greg_Deckler I tried it with your second (A slightly more complex solution), making some small changes to it, and it worked.

Thank you very much 🙂

[Anonymous]

@Greg_Deckler I tried the solution from your post but it's still not working.
I need the Max QTD to show the MAX value of 'QTD' field which is a SUM(qtd) with Fiscal Year and Quarter filters.

Thank you for your response.