# how to get max sum at day grain of value?

• ### Question

• Here's a mockup of a pivot table

```Cust	TranId	Date	Amt	MaxSumAtDayGrain
a	323	1-Jan	5	10
a	423	1-Jan	5	10
a	562	2-Jan	4	10
a	566	2-Jan	5	10
b	325	1-Jan	2	5
b	487	1-Jan	2	5
b	532	2-Jan	4	5
b	599	2-Jan	1	5
```

I'm trying to write some dax to get the MaxSumAtDayGrain scoped to the cust. Basically the max of the amt summed at the day grain and for this to reset for each customer. And for this to also appear at lower levels (eg individual transactions)

I've tried :=MAXX(values('Date'[Date]),calculate(sum([Amt]))) and it gives me the correct value at the cust level, but it falls apart at the lower grains. it just gives me the sum at that level

I'm assuming i need to add a filter of some kind, but every logical combination i've tried has resulted to in the maxsumatdaygrain measure aggregating too many rows or not changing the result at the lower grains.

Thanks

Wednesday, January 14, 2015 6:39 AM

• Hi,

try something like this:
MaxDailyAmtByCustomer:=MAXX(
ALL(Table1[Date]);
CALCULATE(
SUM(Table1[Amt]);
ALLEXCEPT(Table1; Table1[Cust]; Table1[Date])
))

in case you also want to filter to a specific time-period (e.g. year) you may replace
ALL(Table1[Date]) with ALLSELECTED(Table1[Date])

hth,
gerhard

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

• Marked as answer by Friday, January 16, 2015 12:05 AM
Thursday, January 15, 2015 8:06 AM

### All replies

• bump.

sorry about the bump but my account seems to be suffering from this problem: https://social.msdn.microsoft.com/Forums/en-US/a6d4ac86-d862-44d1-8caf-ec2ab348a06f/i-posted-on-c-standard-extensions-and-interop-about-1-h-41-min-ago-but-my-post-doesnt-get?forum=reportabug

my new threads dont appear in the forum view. I need to reply to them to get them to show up in the forums.

Thursday, January 15, 2015 1:31 AM
• Hi,

try something like this:
MaxDailyAmtByCustomer:=MAXX(
ALL(Table1[Date]);
CALCULATE(
SUM(Table1[Amt]);
ALLEXCEPT(Table1; Table1[Cust]; Table1[Date])
))

in case you also want to filter to a specific time-period (e.g. year) you may replace
ALL(Table1[Date]) with ALLSELECTED(Table1[Date])

hth,
gerhard

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

• Marked as answer by Friday, January 16, 2015 12:05 AM
Thursday, January 15, 2015 8:06 AM
• Thanks,

so I'd need to add every col in my main table into the allexcept list if I want it to be filter the result? My real data is giving me a value of 413.35 (max possible should be 24, the amt is number of hrs worked)

It works against the dummy data I supplied so it answers my question.

But this is more of an intermediate step I thought was necessary to actually get what I want. I think i'll start another thread with the full info of what I'm trying to accomplish. Burned ~10hrs on it so far and not quite there yet.

Friday, January 16, 2015 12:05 AM
• on Sub-/GrandTotal my calculation sums max daily values of all customers thats why you see something > 24

to get the max per day and customer try this:

MaxDailyAmtByCustomer:=MAXX(
SUMMARIZE(
Table1;
Table1[Date];
Table1[Cust]);

CALCULATE(
SUM(Table1[Amt]);
ALLEXCEPT(Table1; Table1[Cust]; Table1[Date])
))

hth,
gerhard

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

Friday, January 16, 2015 8:32 AM