Seeking help for Utilization rate calculation and presenting it over a matrix table

[kevinpbi]

I would like to calculate the utilization % of the employees and present the data over a matrix table.

I have the following DAX but it doesn’t seem to tally up correctly.

x_Utilization% =

   CALCULATE(

      SUMX(

         FILTER(

            'Timesheet',

            'Timesheet'[-Status] = "Approved"

         ),

         'Timesheet'[-DurationInHours]

      ) /

      SUMX('DateTable', DateTable[IsWorkHours]) / DISTINCTCOUNT(Timesheet[-EmployeeName]),

      MIN('Employee Data'[-CreatedOn]) <= 'Timesheet'[-ActivityDate] &&

      MAX('Employee Data'[-ModifiedOn]) >= 'Timesheet'[-ActivityDate]

   )

IsWorkHours = IF(DateTable[IsWorkday] = 1, 8, 0)

IsWorkday = SWITCH(WEEKDAY('DateTable'[Date]), 1, 0, 7, 0, 1)

Relationship between databases linked via respective highlighted color.

These are the sample dataset

-EmployeeName	-Country	-CreatedOn	-ModifiedOn	-Status
Andy Colins	Singapore	14-Feb-23	1-Sep-23	Inactive
John Camps	Singapore	24-Mar-22	17-Jan-24	Active
Gerald Singh	Singapore	9-Nov-21	17-Jan-24	Active

-EmployeeName	-ActivityDate	-DurationInHours	-Status	-ActivityType
Gerald Singh	24-Jul-23	1	Approved	Chargeable
Gerald Singh	24-Jul-23	8	Approved	Not Available
Gerald Singh	25-Jul-23	1	Approved	Chargeable
Gerald Singh	25-Jul-23	8	Approved	Not Available
Gerald Singh	26-Jul-23	1	Approved	Chargeable
Gerald Singh	26-Jul-23	8	Approved	Not Available
Gerald Singh	27-Jul-23	1	Approved	Chargeable
Gerald Singh	27-Jul-23	9	Approved	Not Available
Gerald Singh	28-Jul-23	12	Approved	Not Available
Gerald Singh	29-Jul-23	8	Approved	Not Available
Gerald Singh	30-Jul-23	8	Approved	Not Available
Gerald Singh	31-Jul-23	10	Approved	Not Available
Gerald Singh	1-Aug-23	10	Approved	Not Available
Gerald Singh	2-Aug-23	8	Approved	Not Available
Gerald Singh	3-Aug-23	4	Approved	Not Available
Gerald Singh	4-Aug-23	8	Approved	Not Available
John Camps	24-Jul-23	8	Approved	Chargeable
John Camps	25-Jul-23	8	Approved	Chargeable
John Camps	26-Jul-23	8	Approved	Chargeable
John Camps	27-Jul-23	8	Approved	Chargeable
John Camps	28-Jul-23	8	Approved	Chargeable
John Camps	31-Jul-23	8	Approved	Chargeable
John Camps	1-Aug-23	6	Approved	Chargeable
John Camps	1-Aug-23	2	Approved	Not Available
John Camps	2-Aug-23	7	Approved	Chargeable
John Camps	2-Aug-23	1	Approved	Not Available
John Camps	3-Aug-23	6	Approved	Chargeable
John Camps	3-Aug-23	2	Approved	Not Available
John Camps	4-Aug-23	8	Approved	Chargeable

Please note that an employee may not have clocked on a working day. The employee could have clocked multiple times over the same day due to different activity type.

Things that aren’t as I’ve expected. I’ve included the first 2 column data to show calculations

x_Utilization% results

1. Country level should average up to 108.52% ((176hrs [John's hrs] + 206hrs [Gerald's hrs]) / (176 [total working hours for the month] x 2 [employees]))
2. Utilization% of empoyees
   1. Nitin Singh clocked 206 hours. There should be 22 working days for that month multiplied by 8 hours, which is 176 hours. The percentage should be (206/176) = 117.04%. The reason why it is showing up as 21 work days is because, there is one working day which the employee did not clock in at all
   2. The summation of the subcategories are also incorrect. I believe this is caused by multiple entries clocked on the same day by same employee
3. Utilization% of the subcategories are incorrect
   1.   John Camps clocked 101 “Chargeable” hours where it should be (101/176) = 57.38%
   2.      John Camp clocked 75 “Not Available” hours were it should be (75/176) = 42.61%
   3. When you sum both subcategory up, it is appearing correctly at 100%

Apologies for the lengthy post. A beginner to pBI. I've been scratching my head hard for the last 1 week over this formula.

Cheers,

Kevin

[talespin · Accepted solution]

hi @kevinpbi 

I used the sample data which you shared, there are some changes between your and mine data model. Listing everything below.

1. Data model

-There are no limited relationship, like in your datamodel.

-I have used one to many relationship and single direction. Both tables filter Timesheet table.

2.  Used Matrix with Year, month, Country, Empoyee Name and Activity type.

Create these measures and put them in Values. I have verified the numbers per logic shared by you. Please test them thoroughly.

Measure1

Count Working Days =

CALCULATE( COUNT('CALENDAR'[Date]), 'CALENDAR'[IsWorkingDay])

Measure2

Total Duration hours = SUM(Timesheet[DurationInHours])

Measure3

Utilization Pct =

VAR _WorkHrsPerDay = 8

VAR _WorkingDays = [Count Working Days]

VAR _PlannedWrkHrs = (_WorkingDays*_WorkHrsPerDay)

VAR _CountEmployee = CALCULATE(COUNT(DimEmployee[EmployeeName]), REMOVEFILTERS(Timesheet[ActivityType]), DimEmployee[Status] = "Active" )

VAR _AllEmpPlannedWrkHrs = (_PlannedWrkHrs*_CountEmployee)

RETURN DIVIDE([Total Duration hours], _AllEmpPlannedWrkHrs)

[kevinpbi]

Thank you very much for the response. It is now reflecting the correct info. Apart from the DAX, the other problem was also on my database relationship which was set incorrectly. It worked when I followed your model.

[talespin]

You're welcome.

[talespin]

Logic I have used for Working Day in Date table

IsWorkingDay = NOT WEEKDAY( 'CALENDAR'[Date] ) IN { 1,7 }

[Anonymous]

Hi @kevinpbi ,

If I understand correctly, the issue is that you couldn’t calculate the utilization of the employees. Please try the following methods and check if they can solve your problem:

1.Modify the DAX formula for utilization. Enter the following formula.

x_Utilization% = 
CALCULATE(
    SUMX(
        FILTER(
            Timesheet,
            Timesheet[-Status] = "Approved"
        ),
        Timesheet[-DurationInHours]
    ),
    ALL(DateTable), 
    'Employee Data'[-CreatedOn] <= MAX(Timesheet[-ActivityDate]) &&
    'Employee Data'[-ModifiedOn] >= MIN(Timesheet[-ActivityDate])
) / CALCULATE(
    SUMX(
        FILTER(
            DateTable,
            DateTable[IsWorkday] = 1 &&
            DateTable[Date] >= MIN('Employee Data'[-CreatedOn]) &&
            DateTable[Date] <= MAX('Employee Data'[-ModifiedOn])
        ),
        DateTable[IsWorkHours]
    ),
    ALL(Timesheet)
)

If the above ones can’t help you get it working, could you please provide the DateTable raw data(exclude sensitive data) with Text format? It would be helpful to find out the solution. I am not sure about the data inside the Date Table.

You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

Best Regards,

Wisdom Wu

[kevinpbi]

Hi Wisdom,

Thank you for the response. However, the output is still incorrect. Please see below screenshot.

Please see below for the Date Table.

generated via

DateTable =

ADDCOLUMNS(

    CALENDAR(DATE(2022,1,1), DATE(2025,12,31)),

    "Year", YEAR([Date]),

    "Month", FORMAT([Date], "MMMM"),

    "MonthNo", MONTH([Date]),

    "Day", DAY([Date]),

    "Quarter", "Qtr " & FORMAT(QUARTER([Date]), "0")

)