DAX to correctly calculate Staff Utilisation for the company

DAX DAX Calculations
1

Hi brains trust,

I’ve been trying to calculate a staff utilisation for the entire list of employees using DAX to no avail.

While I have been able to utilise DAX to calculate the utilisation % for an individual employee (which is currently set to display with an employee is selected (using a filter), when the dashboard is NOT filtered the utilisation % value becomes the sum of ALL utilisation % values of each individual employee.

As an example, I have a fact table that looks like this:
|Employee|Type|Hours|Revenue
|Employee A|Chargeable Time|24|100
|Employee B|Chargeable Time|16|120
|Employee C|Chargeable Time|24|75

I also have a DAX which calculates the total standard hours in a week
|Employee A|Total Hours|40|
|Employee B|Total Hours|40|
|Employee C|Total Hours|40|

Using DAX I take the sum of the fact table above, and then divide by the total hours (calculated using DAX above)
|Employee A|Utilisation| 60.0%|
|Employee B|Utilisation| 40.0%|
|Employee C|Utilisation| 60.0%|

Also using DAX, I am able to calculate a revenue total
|Employee A|Revenues|2400|
|Employee B|Revenues|1920|
|Employee C|Revenues|1800|

Ultimately I am trying to arrive at a utilisation which will show a value of 53.33%, and NOT 160%.

This desired utilisation value would then help me undertake calculations to:

  • calculate the budgeted direct hours (ie, taking the actual direct hours, divided by the actual utilisation, then multiplying the budgeted utilisation assigned to each employee
  • calculate the budgeted billable revenue (ie, taking the actual billable revenue, divided by the actual utilisation, then multiplying the budgeted utilisation assigned to each employee.

Any guidance or direction would be greatly appreciated!
Utilisation Report Excerpt.pbix (913.8 KB)

2

Hi @mrjkwon,

Welcome to the Forum!

See if this gets you there. I used VALUES to create a virtual table with employees, added a utilisation calculation for each row and averaged that.

image

I hope this is helpful.

1 Like
4

Hello @mrjkwon, good to see that you are having progress with your inquiry. Did the response from @Melissa help you solve your inquiry?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

5

Hi @mrjkwon, we’ve noticed that no response has been received from you since August 26.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Hi @mrjkwon, due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

7

Many thanks for your help Melissa! That worked perfectly.