Forum Discussion

tpal415's avatar
tpal415
Frequent Visitor
6 years ago

Employee Utilization - incorrect totals

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 sta...
Need Help
tpal415's avatar
tpal415
Frequent Visitor
6 years ago

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 Name123456789101112Total
Employee 169%61%66%100%92%103%58%81%6%0%0%0%54%

 

Below is my data:

 

People IDProject IDLogged DateTaskLogged HoursBillable StatusEmployee NameEmployee TitleEmployee Hourly RateEmployee Status
171876642970412Thursday, January 2, 20202. Fieldwork31N/AAnalyst200Full-Time
2627322849029Thursday, January 2, 202002. Business Development40N/AManaging Director & Co-Founder300Full-Time
2627322849029Thursday, January 2, 20200. Admin41N/AManaging Director & Co-Founder300Full-Time
2627442854215Thursday, January 2, 20204. QA61N/ADirector275Full-Time
4421542691602Thursday, January 2, 20202. Internal Audit01N/AManager250Full-Time
170556192855539Thursday, January 2, 2020Onsite Audit Support81N/AConsultant225Full-Time
170900272856177Thursday, January 2, 20202. Internal Audit01N/ASenior Analyst225Full-Time
171876642982088Thursday, January 2, 2020 31N/AAnalyst200Full-Time
2627442856293Thursday, January 2, 20201. Project Planning61N/ADirector275Full-Time
171876642849029Thursday, January 2, 20200. Admin20N/AAnalyst200Full-Time
171876642855500Thursday, January 2, 20202. Fieldwork11N/AAnalyst200Full-Time
171688712856293Thursday, January 2, 20202. Internal Assessments01N/AAnalyst225Full-Time
170900272849029Thursday, January 2, 2020 81N/ASenior Analyst225Full-Time

 

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

  • tpal415's avatar
    tpal415
    Frequent Visitor
    6 years ago

    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!

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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.

    • v-kelly-msft's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi tpal415 ，

       

      Is your issue solved now?

       

       
      Best Regards,
      Kelly
      Did I answer your question? Mark my post as a solution!
      • tpal415's avatar
        tpal415
        Frequent Visitor
        6 years ago

        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: