Skip to main content
cancel
Showing results for 
Search instead 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

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

KGrice
Memorable Member
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.

 

 

WeeklyMax.PNG

 

 

View solution in original post

10 REPLIES 10
AlexChen
Microsoft Employee
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.

 

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

 

 

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?

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

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?

KGrice
Memorable Member
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.

 

 

WeeklyMax.PNG

 

 

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.

 

Vvelarde
Community Champion
Community Champion

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

 

Use Summarize.




Lima - Peru

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors