cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bullius
Helper V
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:

CategoryWeek CommencingAmount
101/01/20155
201/01/201510
301/01/201515
107/01/201510
207/01/201520
307/01/201530
114/01/20152
214/01/20154
314/01/20156

 

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
jahida
Impactful Individual
Impactful Individual

How's something like:

 

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

View solution in original post

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.

 

 

WeeklyMax.PNG

 

 

View solution in original post

9 REPLIES 9
AlexChen
Microsoft
Microsoft

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.

 

5.png

 

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]))

 

6.png

 

Best Regards

Alex

 

 

bullius
Helper V
Helper V

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

jahida
Impactful Individual
Impactful Individual

How's something like:

 

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

View solution in original post

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?

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.

 

 

WeeklyMax.PNG

 

 

View solution in original post

scottsen
Memorable Member
Memorable Member

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
Community Champion
Community Champion

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

 

Use Summarize.




Lima - Peru

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors