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)