topic Re: Overutilised Resources in DAX Commands and Tips

Overutilised Resources

bskiran1711 — Thu, 20 Feb 2025 16:27:46 GMT

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

Re: Overutilised Resources

lbendlin — Fri, 21 Feb 2025 01:07:17 GMT

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 ?

Re: Overutilised Resources

bskiran1711 — Fri, 21 Feb 2025 12:44:50 GMT

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

Re: Overutilised Resources

lbendlin — Fri, 21 Feb 2025 23:01:52 GMT

Re: Overutilised Resources

bskiran1711 — Sat, 22 Feb 2025 12:08:33 GMT

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

Re: Overutilised Resources

lbendlin — Sat, 22 Feb 2025 12:32:02 GMT

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.

Re: Overutilised Resources

bskiran1711 — Sat, 22 Feb 2025 12:54:05 GMT

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

Re: Overutilised Resources

lbendlin — Sat, 22 Feb 2025 14:00:42 GMT

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

Re: Overutilised Resources

bskiran1711 — Sat, 22 Feb 2025 19:07:30 GMT

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

Re: Overutilised Resources

bskiran1711 — Sun, 23 Feb 2025 06:36:57 GMT

Hi,

Would you be able to help me on this?

Thanks

Kiran

Re: Overutilised Resources

lbendlin — Sun, 23 Feb 2025 11:54:21 GMT

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

Re: Overutilised Resources

bskiran1711 — Sun, 23 Feb 2025 11:58:28 GMT

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