Calculating Man Hour Utilization for each employee by Labor Type.

DaveHy — Mon, 20 Mar 2023 09:52:50 GMT

I have built my report that tracked entries from different "Home Stations" around the world. Those reports feed my PBI.

The reports log, Date of task, Labor type ( Direct, Indirect, and Training) along with the time spent on that task. " Duration".

The reports are by Site and not by employee, "Supporting FSR". Some employees are no longer on contract, but I still need to report on their past activities

Here are my tables :

Appended queries of all 34 reports broken down, Each Query look exactly the same. See example table below.

Date	Home Station	Supporting FSR	Labor Type	Contact Type	Category	Duration (Hr)
1/1/23	Okinawa	Joe Suffy	Direct	Support Requested	Engine	3.0
1/2/23	Camp Pendleton	Chris Carson	Indirect	Admin	Report	1.5
1/3/23	Wala Wala, WA	George Jetson	Training	Formal	FLMNET	8.0

(Due to ITAR, CUI, and other regulations I cannot post a Snip of that table.)

Service dates by employee “FSR POP”-

Name	Location	Start Date	End Date
Joe Snuffy	Okinawa	1 April 2022	1 OCT 2022
Chris Carson	Camp Pendleton	6 Jun 2022	28 Feb 2023
George Jetson	Wala Wala, Washingtion	1 April 2022	Current

Tables for each region that sum the duration column and group by “Supporting FSR” and then “Labor Type” -column are named just like that as well Supporting FSR- Labor Type -Duratio

Date	Supporting FSR	Home Station	Labor Type	Duration
1/1/23	Joe Snuffy	Okinawa	Direct	5.3
1/1/23	Joe Snuffy	Okinawa	Indirect	1.0
1/1/23	Joe Snuffy	Okinawa	Training	2.7
1/1/23	George Jetson	Wala Wala, WA	Direct	6
1/1/23	George Jetson	Wala Wala, WA	Indirect	1
1/1/23	Chris Carson	Camp Pendleton	Training	7
1/1/23	Chris Carson	Camp Pendleton	Direct	1

(Due to ITARs I cannot post a Snip of that table.)

I need to use these tables to calculate the number of hours assigned M-F, 8 hours per day, between the start and end dates of each person, excluding Federal holidays that I have listed in another table.

Desired Result would look something like this.

Date	Name	Home Station	Labor Type	Assigned Hours	Logged Hours	Utl Rate
1/1/23	Joe Snuffy	Okinawa	Direct	8	5.3	66.2%
1/1/23	Joe Snuffy	Okinawa	Indirect	8	1.0	12.5%
1/1/23	Joe Snuffy	Okinawa	Training	8	2.7	33.7%
1/1/23	George Jetson	Wala Wala, WA	Direct	8	6	75%
1/1/23	George Jetson	Wala Wala, WA	Indirect	8	1	12.5%
1/1/23	George Jetson	Wala Wala, WA	Training	8	0	0%
1/1/23	Chris Carson	Camp Pendleton	Direct	8	1	12.5%
1/1/23	Chris Carson	Camp Pendleton	Indirect	8	7	87.5%
1/1/23	Chris Carson	Camp Pendleton	Training	8	1	12.5%

I am asking a lot and giving a little. I know

Re: Calculating Man Hour Utilization for each employee by Labor Type.

lbendlin — Sun, 19 Mar 2023 22:34:44 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Calculating Man Hour Utilization for each employee by Labor Type.

DaveHy — Mon, 20 Mar 2023 09:54:39 GMT

Do my changes meet the requirements. Please advise

Re: Calculating Man Hour Utilization for each employee by Labor Type.

lbendlin — Mon, 20 Mar 2023 16:58:49 GMT

Please check the sample data for Chris Carson Camp Pendleton. Something's off in the expected result.

You don't need to post any actual data. Sample data will be fine. Remove anything PII or confidential.

topic Re: Calculating Man Hour Utilization for each employee by Labor Type. in DAX Commands and Tips

Calculating Man Hour Utilization for each employee by Labor Type.

Re: Calculating Man Hour Utilization for each employee by Labor Type.

Re: Calculating Man Hour Utilization for each employee by Labor Type.

Re: Calculating Man Hour Utilization for each employee by Labor Type.