cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Josh_BI_UK
Helper II
Helper II

Staff Utilisation based as % of available hours minus annual leave

Hi PWR BI FamilyHeart,

 

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 modelmy Utilisation data model

 

Business Logic:
=============

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
Josh_BI_UK
Helper II
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:
https://www.youtube.com/watch?v=IQ25ATvJBoM&vl=en

 

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 FamilyHeart

View solution in original post

3 REPLIES 3
Josh_BI_UK
Helper II
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:
https://www.youtube.com/watch?v=IQ25ATvJBoM&vl=en

 

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 FamilyHeart

View solution in original post

v-yuta-msft
Community Support
Community Support

@Josh_BI_UK ,

 

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.

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:

 

https://www.youtube.com/watch?v=IQ25ATvJBoM&vl=en

 

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.