cancel
Showing results for
Did you mean:
Helper II

Staff Utilisation based as % of available hours minus annual leave

Hi PWR BI Family,

Your support would be most appreciated on a staff utilisation problem I have. I have read other utilisation post; however, the solutions either don't fit my use case or are a bit too complicated for me to reverse engineer.

I would like to workout each staff members utilisation rate (i.e. hours related to billable activities = Utilised Hours). I'm open to both re-modelling and DAX related solutions.

This might be 1 of a few requests all related to utilisation. I have tried a few DAX calculations; however, I'm not confident they are right and I would appreciate some fresh thinking. I will post links to all the answers and measures in one unifying post at the end.

Quick Background:
===============

Timesheet based data for a Utilisation dashboard. The data model is a simple "starish" schema.

my Utilisation data model

=============

The below notes aren't DAX formulas, instead just an attempet to convey the logic I wish to implement as DAX.

• Utilised Hours = [Hours] with an activity type marked "Yes" within the related table 'DimActivities' [UtilisedActivityYN] column.
• Working Days = 'DimCalendar'[IsWeekdayYN] = "Yes"
• One Work Hr = 0.13333

Note: (0.13333 is the decimal equivalent of 1 working hour based upon 1 working day = 7.5 hrs  i.e. 1/7.5 = 0.13333)

• Annual Leave Taken = Sum('FactTimesheet'[Hours] were activity = "Annual leave")* 'One Work Hr'
• Available Days = Count('Working Days' within month) - 'Annual Leave Taken'
• Utilisation Rate = Sum('FactTimesheet'[Hours] were activity = "Billable" / 'Available Days'
1 ACCEPTED SOLUTION
Helper II

Hints from @v-yuta-msft & @ruthpozuelo helped me to work out a solution, thank you both.

Using RELATEDTABLE the following DAX worked treat:

```Utilised Hours =
CALCULATE(
SUM(FactTimesheets[Hours])*0.13333,
FILTER(RELATEDTABLE('DimActivities'),
'DimActivities'[UtilisedActivityYN]="Yes")
)```

Ruth Pozuelo Martinez ( @ruthpozuelo ) YouTube video really helped me out in regard to this:

I have a few more Utilisation related measure to solve, which I will add the links to this post, as they come.

Thank you PWR BI Family

3 REPLIES 3
Helper II

Hints from @v-yuta-msft & @ruthpozuelo helped me to work out a solution, thank you both.

Using RELATEDTABLE the following DAX worked treat:

```Utilised Hours =
CALCULATE(
SUM(FactTimesheets[Hours])*0.13333,
FILTER(RELATEDTABLE('DimActivities'),
'DimActivities'[UtilisedActivityYN]="Yes")
)```

Ruth Pozuelo Martinez ( @ruthpozuelo ) YouTube video really helped me out in regard to this:

I have a few more Utilisation related measure to solve, which I will add the links to this post, as they come.

Thank you PWR BI Family

Community Support

You may refer to measures like pattern below:

```Utilised Hours =
CALCULATE (
SUM ( FactTimesheets[Hours] ),
RELATED ( DimActivities[UtilisedActivityYN] ) = "Yes"
)
```

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper II

Hi @v-yuta-msft,

Thank you very much for pointing me in the general direction; however, when I used your DAX formula, I got an error.

I think it had to do with using  RELATEDTABLE instead of RELATED .

Ruth’s (@ruthpozuelo) YouTube video really helped me out in regard to this:

Here is the DAX I adjusted used which worked treat:

```Utilised Hours =
CALCULATE(
SUM(FactTimesheets[Hours])*0.13333,
FILTER(RELATEDTABLE('DimActivities'),
'DimActivities'[UtilisedActivityYN]="Yes")
)```

I have a few more Utilisation related measure to solve, which I will add the links to this post, as they come. Thanks again PWRBI Family.

Announcements

Launching new user group features

Learn how to create your own user groups today!