Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |