Forum Discussion
Resource Utilisation - Different Target Percentage
I'm trying to create a utilisation report with two base tables:
One is the time entries broken down by resource and day, with each entry having a time category from the following; Internal, Absence and Billable.
I then have the resource table which has a hard coded % billable time target e.g.
| Resource | Hours Per Week | Description | Starting Day of the Week | Efficiency Percentage |
| Person 1 | 22.5 | 3D 22.5H M-W | Monday | 30% |
| Person 2 | 37.5 | Reg Week | Monday | 75% |
| Person 3 | 30 | 4D 30H M-T | Monday | 70% |
| Person 4 | 22.5 | 3D 22.5H T-T | Monday | 60% |
| Person 5 | 33.75 | 4.5D 33.75H W PM Off | Monday | 50% |
How would I create a gauge looking at actual billable hours as a % of total time vs the efficiency ratio as the target %, I want to be able to slice this based on date ranges and individual resource.
Thanks
Hi snolan175 ,
I can give you an example:
If you want to show the Efficiency Percentage of Person 1, just use this DAX to create a measure:Measure = CALCULATE( MAX('Table'[Efficiency Percentage]), 'Table'[Resource] = "Person 1" )If you need to calculate other Efficiency Percentages, please explain your calculation process in detail. I can try to write DAX for you.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Can you share the format of both tables with only one resource data?
Hi snolan175 ,
First of all, based only on the information you have provided, I cannot understand what results you need to get. It would be helpful if you could provide the complete sample data for both tables and the desired results you ultimately want to get!
Also, a gauge can only display one piece of data, so if you want all the data in your table to be displayed in gauges, you may need to create multiple gauges, unless you want to display the sum of all the Resources. Please consider using a table or matrix visual object.
Best Regards,
Dino TaoHi Dino,
I want to know how to set the target on a gauge or KPI chart to be the resource's efficiency percentage from the table above which is their target billable time, I have managed to calculate the actual billable time, but just want to add the target but not sure how to create a measure to show the % from the table.
Hi snolan175 ,
I can give you an example:
If you want to show the Efficiency Percentage of Person 1, just use this DAX to create a measure:Measure = CALCULATE( MAX('Table'[Efficiency Percentage]), 'Table'[Resource] = "Person 1" )If you need to calculate other Efficiency Percentages, please explain your calculation process in detail. I can try to write DAX for you.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
