Resource Utilisation - Different Target Percentage

[snolan175]

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

[v-junyant-msft]

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 Tao

[snolan175]

Hi 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. 

[v-junyant-msft · Accepted solution]

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.

[snolan175]

Thanks Dino, I ended up using the selected value function rather than an absolute:

Target Utilisation = CALCULATE(MAX(Efficiency[Efficiency Decimal]),

    FILTER(Resource_Card_Excel,Resource_Card_Excel[No] = SELECTEDVALUE(Resource_Card_Excel[No]))

)

[Musadev]

Can you share the format of both tables with only one resource data?