Forum Discussion
Seeking help for Utilization rate calculation and presenting it over a matrix table
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.
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
- 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]))
- Utilization% of empoyees
- 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
- The summation of the subcategories are also incorrect. I believe this is caused by multiple entries clocked on the same day by same employee
- Utilization% of the subcategories are incorrect
- John Camps clocked 101 “Chargeable” hours where it should be (101/176) = 57.38%
- John Camp clocked 75 “Not Available” hours were it should be (75/176) = 42.61%
- 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
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])Measure2Total Duration hours = SUM(Timesheet[DurationInHours])Measure3Utilization Pct =VAR _WorkHrsPerDay = 8VAR _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)
6 Replies
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
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"))
- talespin
Solution Sage
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])Measure2Total Duration hours = SUM(Timesheet[DurationInHours])Measure3Utilization Pct =VAR _WorkHrsPerDay = 8VAR _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)- talespin
Logic I have used for Working Day in Date table
IsWorkingDay = NOT WEEKDAY( 'CALENDAR'[Date] ) IN { 1,7 } 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.
