Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.