cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shwam Regular Visitor
Regular Visitor

Help needed: Calculate the MAX of SUMMED values

Hi All,

 

To start off, my sql and DAX skills are not advanced.

 

I am working with data from the fulfilment area of a bank.

 

Thousands of applications come in daily and the database is saving the history of each move as the work item moves through queues.

We are calculating the man power needed to work each queue per day and per month.

 

The way I have worked out the daily resources needed is by assigning each queue a Resources Required figure.

To work out the daily resources required, I sum the Resources Required figure per queue to give me the total needed per day.

 

This works perfectly for a daily view.

I can clearly see the theoretical amount of resources needed per day.

 

Now, when I drill up to monthly, I can only see the sum of all of the days in the month which gives a figure which is far too large.

 

For example (pretending only 3 days in a month): Day 1- 20 people needed, Day 2 - 30 people needed, Day 3 - 25 people needed.

 

When I drill up to monthly and sum is selected, the total is 75 which is far too large.

And when I select max it will give me an extremely small figure such as 0.02 (largest of the small figures used to get the sum of 20, 30 or 25 as in the example above).

 

My goal is to display the max of the daily summed values in the monthly view.

So with regards to the "example" above, I would like the Day 2 figure of 30 to display as the max for the month - the largest summed daily value shoudl reflect as the max on the monthly view.

 

Please assist and try keep your answers as basic as possible 🙂

 

Read many other threads and they're a bit too advanced for me.

 

Thanks!

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Shwam Regular Visitor
Regular Visitor

Re: Help needed: Calculate the MAX of SUMMED values

6 REPLIES 6
Shwam Regular Visitor
Regular Visitor

Re: Help needed: Calculate the MAX of SUMMED values

Bump: please help
v-huizhn-msft Super Contributor
Super Contributor

Re: Help needed: Calculate the MAX of SUMMED values

Hi @Shwam,

It's difficult to reproduce your scenario based on your description? Is there any chance to share your sample data table and list exected result in details, so we can post the solution which is close to your requirement. Thanks for understanding.

Best Regards,
Angelia

Shwam Regular Visitor
Regular Visitor

Re: Help needed: Calculate the MAX of SUMMED values

Don't know how to attach a spreadsheet.

 

For example:

 

Just say I have multiple line items for every day of the month and when I build a graph I select my values as a sum.

 

Day 1: 5min, 10 min, 5 min. sum = sum 20 min

 

Day 2: 2 min, 10 min, 5 min = sum 17 min

 

Day 3: 10 min, 10 min, 20 min = sum 40 min

 

when looking at it daily, the sum amounts per day is what I want.

But when I drill up to monthly and sum is selected itll show 77 min. (which is not what I'm looking for).

If i Select max for the monthly view it will show 20 min (the largest daily value).

 

I want the max value to show the largest sum of the daily values which would be the sum of day 3 = 40 min.

Shwam Regular Visitor
Regular Visitor

Re: Help needed: Calculate the MAX of SUMMED values

My biggest problem is that the MAX function can only accept a column and not a measure.

So i cannot write a function as MAX(SUM(values).

 

My goal is to sum the daily values into a summed value per day and then for the monthly view show the MAX of the summed values per day.

Shwam Regular Visitor
Regular Visitor

Re: Help needed: Calculate the MAX of SUMMED values

Anybody done this before?

Shwam Regular Visitor
Regular Visitor

Re: Help needed: Calculate the MAX of SUMMED values

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 270 members 2,989 guests
Please welcome our newest community members: