How to calculate utilization per employee per month

[ankitkalsara]

Hi team,

I would like to calculate employee's utilization per month with the below data.

Employee Name	Month	Work Date	WorkingDaysPerMonth	Utilization Per Emp
Emp 1	Sep 2021	1st Sep	22	9.09%
Emp 1	Sep 2021	3rd Sep	22	9.09%
Emp 2	Sep 2021	5th Sep	22	13.63%
Emp 2	Sep 2021	7th Sep	22	13.63%
Emp 2	Sep 2021	9th Sep	22	13.63%
Emp 3	Sep 2021	15th Sep	22	4.54%

I need help to create below 2 measures:

• Utilization per Employee: Each employee works for different number of days and the utilization per employee = number of working days for each employee/Working Days Per Month. (e.g. Emp 1 worked for 2 days - 2/22 = 9.09%, emp 2 worked for 3 days - 3/22 = 13.63%)
• Avg Utilization per month: ( 9.09% + 13.63% + 4.54% ) / 3 = 9.08%

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Greg_Deckler]

@ankitkalsara See if the Utilization recipe from my book, DAX Cookbook helps. It is recipe 1 in Chapter 8 I believe: DAXCookbook/Ch8 at master · gdeckler/DAXCookbook (github.com)

[ankitkalsara]

Hi @Greg_Deckler,

It is not giving me accurate results.

I have got the below data and I am using Date table.

I need to determine overall utilization per employee and by month.

From the above table, I need to determine unique shift date for each employee.

e.g. Emp1 has worked total of 4 days (need to count only unique days - a day can have multiple tasks assigned to employee)

Then from the date table, I need to count total number of working days for the given month.

i.e. for Jan - working days = 21

Utilization per employee should be

• Emp1 - 4days/21 = 19.04%
• Emp2 - 2days/21 = 9.52%
• Emp3 - 1day/21 = 4.76%

Monthly Avg utilization = (19.04+9.52+4.76)/3 = 11.10%

[ankitkalsara]

Hi @Greg_Deckler ,

Attached is the test pbix file.

[V-lianl-msft · Accepted solution]

Try to create measure like below:

Utilization = AVERAGEX(VALUES(Shifts[Emp Name]), DIVIDE([Total Working Days],SUM('Date'[Working Days Per Month])))