cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vinayrg
Frequent Visitor

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

I have the below data. 

Fiscal YearFiscal QtrqtdTotal QTDMax QTD
2020Q11010040
2020Q22010040
2020Q33010040
2020Q44010040
2021Q158035
2021Q2158035
2021Q3258035
2021Q4358035

 

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
Greg_Deckler
Super User IV
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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
CNENFRNL
Super User III
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]
)

Screenshot 2021-02-17 202920.png

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

 

Could you please help me acieve this?

Thanks in advance.

 

Greg_Deckler
Super User IV
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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.