Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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

 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.