cancel
Showing results for
Did you mean:
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

5 REPLIES 5
Super User III

@vinayrg , 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]
)``````

Frequent Visitor

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

Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

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

Frequent Visitor

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

Announcements