Employee Utilization - incorrect totals

[tpal415]

I am trying to figure out how to build a formula that will provide the utilization for multiple employees reporting different billable hours and also with different working hours due to when they started. Hours are reported by day with multiple employees in the same column and hours in another column. I've calculated working hours within a master calendar table.

I'm trying to divide billable hours by working hours, using the calendar to dynamically filter by month, quarter, etc. Ideally I would like to use this to have each employee on a row with utilization and then the total being entire utilization.

Below is the utilization table, but totals are incorrect calculated as such:

• Employee Utilization = iferror([Billable Hours] / sum('Calendar'[WorkHours]), blank())

Billable hours and work hours are measures: 

• Billable Hours = CALCULATE(SUM('2 Hours'[Logged Hours]),FILTER('2 Hours',[Billable Status]="1"))

• WorkHours = VAR WeekDayNum =  WEEKDAY ('Calendar'[Date] )
  RETURN (IF (OR ( OR ( WeekDayNum = 1, WeekDayNum = 7 ), RELATED ( '6 Holidays'[Holiday Start Date[Date] ) <> BLANK ()),0, 8))

Utilization by month

The row values seem to be very close, but I believe it is calculating each employees billable hours by the total available hours and not totaling correctly.

[tpal415]

@Greg_Deckler Thank you for the quick reply! I utilized your recipe and it worked great, the totals are correct and much easier. My only issue is if an employee had not started it still divides total billable hours by total work hours, resulting in inacurratly low utilization. How can I filter out my total working hours for each unique individual employee?

Here are the calculations I used from your recipe:

% Utilization = 
    VAR __Table = SUMMARIZE('2 Hours','2 Hours'[Employee Name]) 
    VAR __TotalBillableHours = SUMX(__Table,[Total Billable Hours]) 
    VAR __TotalHours = SUMX(__Table,[Total Hours])
    VAR __Utilization = DIVIDE(__TotalBillableHours,__TotalHours,0)
RETURN
    IF(ISBLANK(__Utilization),0,__Utilization)

Total Billable Hours = 
    var _Category = MAX('2 Hours'[Billable Status])
Return    
IF(
    _category = "0",
        0,
        SUMX(
            FILTER(
                '2 Hours','2 Hours'[Billable Status] = "1"
            ),
            '2 Hours'[Logged Hours]
        )
    )

Total Hours = 
    switch(max('2 Hours'[Column]),
    "Full-Time", SUM('Work Hour Calendar'[Work Hours]),
    blank()
)

Here is the result:

Employee Name	1	2	3	4	5	6	7	8	9	10	11	12	Total
Employee 1	69%	61%	66%	100%	92%	103%	58%	81%	6%	0%	0%	0%	54%

Below is my data:

People ID	Project ID	Logged Date	Task	Logged Hours	Billable Status	Employee Name	Employee Title	Employee Hourly Rate	Employee Status
17187664	2970412	Thursday, January 2, 2020	2. Fieldwork	3	1	N/A	Analyst	200	Full-Time
262732	2849029	Thursday, January 2, 2020	02. Business Development	4	0	N/A	Managing Director & Co-Founder	300	Full-Time
262732	2849029	Thursday, January 2, 2020	0. Admin	4	1	N/A	Managing Director & Co-Founder	300	Full-Time
262744	2854215	Thursday, January 2, 2020	4. QA	6	1	N/A	Director	275	Full-Time
442154	2691602	Thursday, January 2, 2020	2. Internal Audit	0	1	N/A	Manager	250	Full-Time
17055619	2855539	Thursday, January 2, 2020	Onsite Audit Support	8	1	N/A	Consultant	225	Full-Time
17090027	2856177	Thursday, January 2, 2020	2. Internal Audit	0	1	N/A	Senior Analyst	225	Full-Time
17187664	2982088	Thursday, January 2, 2020		3	1	N/A	Analyst	200	Full-Time
262744	2856293	Thursday, January 2, 2020	1. Project Planning	6	1	N/A	Director	275	Full-Time
17187664	2849029	Thursday, January 2, 2020	0. Admin	2	0	N/A	Analyst	200	Full-Time
17187664	2855500	Thursday, January 2, 2020	2. Fieldwork	1	1	N/A	Analyst	200	Full-Time
17168871	2856293	Thursday, January 2, 2020	2. Internal Assessments	0	1	N/A	Analyst	225	Full-Time
17090027	2849029	Thursday, January 2, 2020		8	1	N/A	Senior Analyst	225	Full-Time

Thank you for any help, this has already got me on the right track!

[tpal415]

@Greg_Deckler 

Still haven't quite figured out how to incorporate start and end dates into total work hours for each employee using your utilization formula. I bought and read through the utilization chapters within your book, but cannot see an explanation on how to calculate work hours by individual employee, only an aggregation of total possible working hours. Any help would be appreciated!

[v-kelly-msft]

Hi @tpal415 ，

Is your issue solved now?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[tpal415]

@Kelly Unfortuantely no.  I believe my issue is with calucalting total hours available. I can't quite figure out how to calculate the total work hours available by each individual employee when employees have different start dates. I have a calendar with the total work hours and another table with employee start dates, but cannot tie the two together. The billable hours is correct, and totals are all summing correclty, but below are the outputs:

Total Hours:

#TotalHours = 
    VAR __Start = MAX('7 People'[Start Date])
RETURN
    switch(max('2 Hours'[Employee Type]),
    "Full-Time", SUMX(FILTER('Work Hour Calendar',[Date]>__Start),'Work Hour Calendar'[WorkHours]),
    blank()
)

Output:

*Here is the issue - it's good that only the numbers are showing up in months where people have input hours, but again it's taking the entire month of work hours, not by day and also only months 7-12 which is most likely because the most recent (max) start date was mid July. This obviously throws everything else off.

Total Hours by Employee:

#TotalHoursByEmployee = SUM('Work Hour Calendar'[WorkHours])

Output:

Total Billable Hours:

Total Billable Hours = 
    var _Category = MAX('2 Hours'[Billable Status])
Return    
IF(
    _category = "0",
        0,
        SUMX(
            FILTER(
                '2 Hours','2 Hours'[Billable Status] = "1"
            ),
            '2 Hours'[Logged Hours]
        )
    )

Output:

 Utilization:

%Utilization = 
VAR __utilization = DIVIDE([Total Billable Hours],[#TotalHours],0)
VAR __days = countrows('2 Hours')
RETURN
    IF(ISINSCOPE('7 People'[Employee Name]),
        IF(
            Isblank(__Days),
            BLANK(),
            IF(
                ISBLANK(__utilization),
                0,
                __utilization
            )),
        VAR __tempTable = 
            SUMMARIZE(
                '2 Hours',
                '2 Hours'[Employee Name],
                "__billableHours",[Total Billable Hours],
                "__totalHours",[#TotalHours]
            )
        VAR __totalBillableHours = SUMX(__tempTable,[__billableHours])
        VAR __totalTotalHours = SUMX(__tempTable,[__totalHours])
        RETURN
            DIVIDE(__totalBillableHours,__totalTotalHours,0)
)

Output:

[v-kelly-msft]

Hi  @tpal415 ,

It's a subtotal issue,a very common issue,could you pls upload your .pbix file to Onedrive business and share the link with me?(Remember to remove the confidential information)

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

@tpal415 - See if this helps, attached PBIX from my other book, Learn Power BI which goes through the whole utilization stuff in detail.

I am thinking maybe:

Total Hours = 
    VAR __Start = MAX('Employee Table'[Start Date]),
RETURN
    switch(max('2 Hours'[Column]),
    "Full-Time", SUMX(FILTER('Work Hour Calendar',[Date]>__Start),[Work Hours]),
    blank()
)

Something like that.

[Greg_Deckler]

@tpal415 - I have a utilization recipe in my book, DAX Cookbook, it is Recipe 1, Chapter 8. 

https://github.com/gdeckler/DAXCookbook

If you can post sample data that would help. 

Looks like you measure total is off. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Finally, since I see you are a New Member, please check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.