cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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

Frequent Visitor

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors