Sum of Max values

[khush19]

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?

[Greg_Deckler]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[khush19]

Thanks Ashish,

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

[Ashish_Mathur]

You are welcome.