Overutilised Resources

[bskiran1711]

I have the below sample data

Name	Dt	Total Worked Hours	Alloted Production Hours
A1	2/1/2025	75	70
A1	2/2/2025	80	85
A1	2/3/2025	95	90
A2	2/1/2025	65	60
A2	2/2/2025	70	75
A2	2/3/2025	68	65
A3	2/1/2025	70	75
A3	2/2/2025	80	85
A3	2/3/2025	90	95

I need the below transformation of the data

Total Worked Hours	Alloted Prod Hours	Resources Over Utilised	Unutilised Prodn Hours	Total Emp
693	700	2	7	3

I am stuck in calculating the Over utilised resources, the tricky part is there would be slicers as well. So while calculating Over utilised resources, Alloted Prod Hours needs to be aggregated along with Total Worked Hours for each Employee for the selected date. Then check how many employees are having efficiency more than 1. Efficiency is Total Worked Hours / Alloted Prodn Hours. Please help me in achieving the above result

[bskiran1711]

Hi,

Would you be able to help me on this?

Thanks

Kiran

[lbendlin]

Your new requirements are unclear for me. 
Please show the expected outcome based on the sample data you provided.

[bskiran1711]

Hi,

My sincere apologies.

If 2/1/2025 is selected in Slicer,

A1 has worked for 5 hours more,

A2 has worked for 5 hours more

A3 has worked for 5 hours less

A4 has worked for 5 hours more

So A1 , A2 and A4 are overutilised. Since A1 is Mgmt resource and should not be counted as Production resource, Overutilised resources are 2.

Similarly if 2/1/2025 and 2/2/2025 is selected

For A1, A2 and A3 have not worked for more than Alloted Production hours,A4 has worked 8 hours more, hence overutilised is 1

Thanks

Kiran

[lbendlin]

What does "over utilized"  mean?  Worked more than allotted? Does that reset each day or is it cumulative?

So A1 and A2 are overutilized by a cumulative 8 hrs and 3 is underutilized by 15  resulting in a total of 7 ?

[bskiran1711]

Hi,

Thanks for the reply, If 2/1/2025 is selected in Slicer,

A1 has worked for 5 hours more,

A2 has worked for 5 hours more

A3 has worked for 5 hours less

So A1 and A2 are overutilised. Hence Overutilised resources are 2.

Similarly if 2/1/2025 and 2/2/2025 is selected

For A1, A2 and A3 have not worked for more than Alloted Production hours, Hence overutilised is 0

Thanks

Kiran

[lbendlin]

[bskiran1711]

Hi,

Thanks for the solution, There is an additional condition that Mgmt resources should not be considered while calculating Overutilised.

Request your kind assistance in this regard.

A1 is a Mgmt resource helping in production so resource type Mgmt should be excluded. 

Thanks

Kiran

[lbendlin]

That is not clear from the sample data. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[bskiran1711]

Hi,

My sincere apologies.

If 2/1/2025 is selected in Slicer,

A1 has worked for 5 hours more,

A2 has worked for 5 hours more

A3 has worked for 5 hours less

A4 has worked for 5 hours more

So A1 , A2 and A4 are overutilised. Since A1 is Mgmt resource and should not be counted as Production resource, Overutilised resources are 2.

Similarly if 2/1/2025 and 2/2/2025 is selected

For A1, A2 and A3 have not worked for more than Alloted Production hours,A4 has worked 8 hours more, hence overutilised is 1

Thanks

Kiran

[lbendlin]

How does this impact the other metrics you need? Will "Unutilized Production Hours"  and "Total Employees"  now be calculated differently too?

[bskiran1711]

Ideally they should change, but our Leadership  team wants to see Mgmt resources Production Hours to be aded in Aggregate.