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

Helper V

## MAX of SUM values

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

2 ACCEPTED SOLUTIONS
Impactful Individual

How's something like:

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

Memorable Member

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.

10 REPLIES 10
Microsoft Employee

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

Anonymous
Not applicable

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

Helper V

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

Impactful Individual

How's something like:

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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!

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?

Memorable Member

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
Not applicable

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.

Community Champion

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

Use Summarize.

Lima - Peru