Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Business Logic:
=============
The below notes aren't DAX formulas, instead just an attempet to convey the logic I wish to implement as DAX.
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)
Solved! Go to Solution.
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 Family
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 Family
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.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |