cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

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

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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