cancel
Showing results for
Search instead for
Did you mean:
Resolver I

## Sum of Max values

Hi,

I have below table ,note it is just the smaple data

I want to calculate SUM on top of MAX.

Max is calculated on grouping CustomerID,AreaID and date,and once max is calulated need to sum of data based on CUstomerID and Date.

 CustomerID AreaID Date Amount Max Amount SUM Amount 1 x 6/04/2020 10 10 40 1 y 6/04/2020 20 30 40 1 y 6/04/2020 30 30 40 1 x 5/04/2020 10 20 20 1 x 5/04/2020 20 20 20 2 u 6/04/2020 10 20 50 2 z 6/04/2020 5 30 50 2 u 6/04/2020 20 20 50 2 z 6/04/2020 30 30 50 2 z 6/04/2020 15 30 50

in my reports I will have card which will display Total Amount for a day for a customer.

So if CustomerID=1 and date is 6/04/2020 then card should display 40

if CustomerID=1 and date is 5/04/2020 then card should display 20

if CustomerID=2 and date is 6/04/2020 then card should display 50

I am able to calculate MAX,below is my measure:

MaxAmount =
CALCULATE(MAX(RealReport[Amount]),ALLEXCEPT(RealReport,RealReport[CustomerID],RealReport[AreaID],RealReport[Date]))

I tried to calculate SUM,but it is not coming as expected:

TotalCalculatedAmount =
SUMX(ALLEXCEPT(RealReport,RealReport[CustomerID],RealReport[Date]),[MaxAmount])

I also tried :
Measure =
SUMX
(SUMMARIZE(RealReport,RealReport[CustomerID],RealReport[Date],"Max Amount",CALCULATE(MAX(RealReport[Amount]),ALLEXCEPT(RealReport,RealReport[CustomerID],RealReport[date],RealReport[AreaID]))),[Max Amount])

How to get sum?

1 ACCEPTED SOLUTION
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
4 REPLIES 4
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Resolver I

Thanks Ashish,

I never thought measures you created will work as it looks so neat and simple,it was great learning

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

## Helpful resources

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors