MAX of SUM values

[bullius]

Hello

I am trying to get the MAX of weely SUMs for last year. Any ideas for what I could use as a measure?

Simplified data looks like this:

Category	Week Commencing	Amount
1	01/01/2015	5
2	01/01/2015	10
3	01/01/2015	15
1	07/01/2015	10
2	07/01/2015	20
3	07/01/2015	30
1	14/01/2015	2
2	14/01/2015	4
3	14/01/2015	6

So, SUM for W/C 01/01/2015 = 30, W/C 07/01/2015 = 60, W/C 07/01/2015 = 12

I need a measure that returns 60. I have tried:

Max Weekly Total Last Year = MAX(CALCULATE(SUM(Table1[Amount]), FILTER(Table1, [Week Commencing] = Table1[Week Commencing])))

Any thoughts?

Thanks

[bullius]

I am trying to get the MAX of weely SUMs for last year. Any ideas for what I could use as a measure?

Simplified data looks like this:

Category	Week Commencing	Amount
1	01/01/2015	5
2	01/01/2015	10
3	01/01/2015	15
1	07/01/2015	10
2	07/01/2015	20
3	07/01/2015	30
1	14/01/2015	2
2	14/01/2015	4
3	14/01/2015	6

So, SUM for W/C 01/01/2015 = 30, W/C 07/01/2015 = 60, W/C 07/01/2015 = 12

I need a measure that returns 60. I have tried:

Max Weekly Total Last Year = MAX(CALCULATE(SUM(Table1[Amount]), FILTER(Table1, [Week Commencing] = Table1[Week Commencing])))

Any thoughts?

Thanks

[jahida · Accepted solution]

How's something like:

MAXX(SUMMARIZE(Table1, Table1[Week Commencing], "Amount", SUM(Table1[Amount])), [Amount])

[AlexChen]

Hi,

I assume your table called “categoryInfo”.

You can create a column called “weekOfYear” to get the week of year from “Week Commencing” in Query Editor. See screenshot below.

Now you can called a measure to calculate the sumAmount by weekOfyear:

sumAmountByWeek = CALCULATE(sum(categoryInfo[Amount]), ALLEXCEPT(categoryInfo, categoryInfo[WeekOfYear]))

Then you can create a measure to calculate max sumAmount:

maxSumWeekAmount = CALCULATE(MAXX(ALL(categoryInfo[WeekOfYear]), categoryInfo[sumAmountByWeek]))

Best Regards

Alex

[gideonzedler]

Hi, how do I use slicers to limit the output for this measure? How do I display the year/week this output belongs to?

[bullius]

Thanks! I was unfamiliar with the MAXX function, which seems to be the key.

[jahida · Accepted solution]

How's something like:

MAXX(SUMMARIZE(Table1, Table1[Week Commencing], "Amount", SUM(Table1[Amount])), [Amount])

[Anonymous]

It is such a powerful functions! Thank you so much @jahida 

[Anonymous]

For those using @jahida's method, I just want to point out that "Amount" is the name of the column you are summarizing; it is not a made up column name. I missed this point initially, and I thought this function wasn't working for me.

MaxAmount = MAXX(SUMMARIZE(Table1, Table1[Week Commencing], "Amount", SUM(Table1[Amount])), [Amount])

For example, here is how my function looks; "Principal Balance" is a column within the Deals table, I didn't just make up that column name.

MaxPrinBal = MAXX(SUMMARIZE(Deals, Deals[Close Date].[Year], "Principal Balance", SUM(Deals[Principal Balance])), [Principal Balance])

Thank you @jahida for posting this answer, it works perfect!

[Clara]

Hey guys! Sorry for bringing this thread back from the dead. Just a follow-up question: is there any way I could extract which group (in this case, which week) corresponds to the max summarized value?

[KGrice · Accepted solution]

If you already have a standard measure to sum your amount column, i.e., SUM(Table1[Amount]), you can also use

WeeklyMax = MAXX(VALUES(Table1[Week Commencing]), [SumAmount])

This will return 60 in the totals row or when you have multiple weeks in one evaluation, but will return the week's sum otherwise.

[Anonymous]

KGrice nailed it.

Only note I would make is that... before you get TOO crazy with your model, you are going to want a separate calendar table at some point... (and relate that back to this fact table).  This will easily allow you to aggregate by week/month/year/qtr, handle prior month deltas, etc.

[Vvelarde]

You need to summarize by week and then obtain the max sum.

Use Summarize.