cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MikeGYYC Frequent Visitor
Frequent Visitor

Sum of Filtered Values

Hi All,

 

I'm running into one issue with a dashboard I'm building and it's breaking my whole model. First off, I'm dealing with multiple rows of data, each of which is time entry information for various employees, here's a sample of how the data looks:

 

CustomerDateTaskTimeBilling StatusEmployeeRegion
Customer X2/22/2019Travel4BillableEmployee ASales Region A
Customer X2/24/2019Do Work6BillableEmployee ASales Region A
Customer Y2/25/2019Admin1.5BillableEmployee ASales Region A
Customer Y2/25/2019Work8BillableEmployee ASales Region A
Customer Z2/26/2019Admin8BillableEmployee ASales Region A
Customer Y2/26/2019Work2BillableEmployee ASales Region A
Customer A2/27/2019Travel8BillableEmployee ASales Region A
Customer Y2/28/2019Work8BillableEmployee ASales Region A
Customer X3/1/2019Admin6BillableEmployee ASales Region A
Customer X3/4/2019Travel1.5BillableEmployee ASales Region A
Customer X3/12/2019Work2BillableEmployee ASales Region A
Cemtomer Y3/12/2019Work1BillableEmployee ASales Region A
Customer X3/15/2019Work2.5BillableEmployee ASales Region A
Customer X3/19/2019Admin0.5BillableEmployee ASales Region A
Customer Y3/25/2019Work0.5BillableEmployee ASales Region A
Customer Y3/27/2019Admin1.5BillableEmployee ASales Region A
Customer A2/22/2019Travel4BillableEmployee BSales Region A
Customer B2/24/2019Do Work3BillableEmployee BSales Region A
Customer B2/25/2019Admin1BillableEmployee BSales Region A
Customer A2/25/2019Work2BillableEmployee BSales Region A
Customer C2/26/2019Admin0.5BillableEmployee BSales Region A
Customer D2/26/2019Work1.5BillableEmployee BSales Region A
Customer B2/27/2019Travel7BillableEmployee BSales Region A
Customer A2/28/2019Work3BillableEmployee BSales Region A

 

(There are some other columns, but I'm mostly concerned about the date, employee and sales region)

 

What I'm trying to do is calculate billable utilization based on a standard measurement of 173 hours available to work in a month.

 

Here's what I've done so far. I've started by calculating how many months an employee has entered time by using the following measures:

 

Minimum DATE = MONTH(MIN( 'Utilization Report' [Date]))

Maximum Date = MONTH(MAX('Utilization Report' [Date]))

Active Working Months = IF(([Maximum Date] - [Minimum Date])=0,1,(([Maximum Date]-[Minimum Date])+1))

 

I've then created a measure to calculate the Available Time an Employee has to work in a month using the following measure:

 

Available Time Per Employee = (([Active Working Months])*173

 

This gets me to my challenge.  On an individual by individual basis when results are filtered, I can calculate billable utilization, but when I try and calculate utilization of a broader group of employees (i.e. by the sales region, or as a whole department) I'm getting incorrect values becaue the total has no filter. Here's an example of a visual table for one of the sales regions:

Sales Region.png

When looking at the whole region, on an idividual by indivdiual basis, the formulas are working correctly (e.g. an employee works 3 months, so 3*173 = 520 hours and I can calculate the billable utilization by Summing hours with a filter for the appropriate time category divided by the available hours.

 

However the total for the region is incorrect because it has no filter (that's what column 1Test is showing). The total I want to get is 4,853 hours (i.e. the sum of the individuals) considering that there is one individual that only worked for one month in the filtered time period. But when I try and use a measure like 

 

Sales Region Available Time = DISTINCTCOUNT('Utilization Report'[Employee])*[Available Time Per Employee]

 

I get an over-represented total (i.e. 10*520 hours) rather than the (9*520 +1*173) that I'm looking for. I've tried using a couple of different SUMX formulas, but I keep running into the fact that the number of individual rows of data in the base table is massive and I need the total for a region or department to be filterable to different date ranges. If anybody has any suggestions on how I could potentially sum the values of a measure to get the total that I'm looking for, I'd greatly appreciate it. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Sum of Filtered Values

I would suggest trying the following change on the Active Working Months measure:

Active Working Months = SUMX( VALUES('Utilization Report'[Employee]), ([Maximum Date]-[Minimum Date])+1)

This will mean that if you have 1 employee with 3 months and another with 2 months available at the total/regional levels this will add up to 5 months, then the multiplication by 173 will work

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Sum of Filtered Values

I would suggest trying the following change on the Active Working Months measure:

Active Working Months = SUMX( VALUES('Utilization Report'[Employee]), ([Maximum Date]-[Minimum Date])+1)

This will mean that if you have 1 employee with 3 months and another with 2 months available at the total/regional levels this will add up to 5 months, then the multiplication by 173 will work

View solution in original post

MikeGYYC Frequent Visitor
Frequent Visitor

Re: Sum of Filtered Values

You sir are a scholar and a gentleman. That works perfectly! Thank you so much!

Helpful resources

Announcements
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.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

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: 243 members 2,432 guests
Please welcome our newest community members: