Sum of Filtered Values

[MikeGYYC]

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:

Customer	Date	Task	Time	Billing Status	Employee	Region
Customer X	2/22/2019	Travel	4	Billable	Employee A	Sales Region A
Customer X	2/24/2019	Do Work	6	Billable	Employee A	Sales Region A
Customer Y	2/25/2019	Admin	1.5	Billable	Employee A	Sales Region A
Customer Y	2/25/2019	Work	8	Billable	Employee A	Sales Region A
Customer Z	2/26/2019	Admin	8	Billable	Employee A	Sales Region A
Customer Y	2/26/2019	Work	2	Billable	Employee A	Sales Region A
Customer A	2/27/2019	Travel	8	Billable	Employee A	Sales Region A
Customer Y	2/28/2019	Work	8	Billable	Employee A	Sales Region A
Customer X	3/1/2019	Admin	6	Billable	Employee A	Sales Region A
Customer X	3/4/2019	Travel	1.5	Billable	Employee A	Sales Region A
Customer X	3/12/2019	Work	2	Billable	Employee A	Sales Region A
Cemtomer Y	3/12/2019	Work	1	Billable	Employee A	Sales Region A
Customer X	3/15/2019	Work	2.5	Billable	Employee A	Sales Region A
Customer X	3/19/2019	Admin	0.5	Billable	Employee A	Sales Region A
Customer Y	3/25/2019	Work	0.5	Billable	Employee A	Sales Region A
Customer Y	3/27/2019	Admin	1.5	Billable	Employee A	Sales Region A
Customer A	2/22/2019	Travel	4	Billable	Employee B	Sales Region A
Customer B	2/24/2019	Do Work	3	Billable	Employee B	Sales Region A
Customer B	2/25/2019	Admin	1	Billable	Employee B	Sales Region A
Customer A	2/25/2019	Work	2	Billable	Employee B	Sales Region A
Customer C	2/26/2019	Admin	0.5	Billable	Employee B	Sales Region A
Customer D	2/26/2019	Work	1.5	Billable	Employee B	Sales Region A
Customer B	2/27/2019	Travel	7	Billable	Employee B	Sales Region A
Customer A	2/28/2019	Work	3	Billable	Employee B	Sales 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:

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. 

[d_gosbell · Accepted solution]

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

[MikeGYYC]

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