Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
How can you calculate the labour efficiency from a table of labour bookings? See example data and required results below.
The problem I am having is the production standard is per Job Num/Asm/Operation and there is more than one labour booking per operation,
Job Num | Asm Seq | Op Seq | Op Code | Production Std | Labour Hrs | Required Qty | Completed Qty |
10001 | 0 | 10 | 7000 | 0.5 | 0.3 | 4 | 1 |
10001 | 1 | 10 | 6011 | 0.8 | 0.7 | 6 | 6 |
10001 | 2 | 10 | 6011 | 2 | 1.8 | 8 | 8 |
10001 | 0 | 10 | 7000 | 0.5 | 0.4 | 4 | 3 |
10001 | 0 | 20 | 7500 | 2 | 1 | 4 | 2 |
10001 | 0 | 20 | 7500 | 2 | 1 | 4 | 2 |
Expected results: Efficiency = Production Std / Labour Hrs
Job Num | Asm Seq | Operation | Op Code | Production Std | Labour Hrs | Completed Qty | Efficiency |
10001 | 0 | 10 | 7000 | 0.5 | 0.7 | 4 | 71% |
10001 | 0 | 20 | 7500 | 2 | 2 | 4 | 100% |
10001 | 1 | 10 | 6011 | 0.8 | 0.7 | 6 | 114% |
10001 | 2 | 10 | 6011 | 2 | 1.8 | 8 | 111% |
5.3 | 5.2 | 102% |
Have tried many different ways but been unsuccessful, not sure what would be the correct way.
Many thanks for any pointers.
Regards
Richard
Solved! Go to Solution.
You may add the following measures.
Measure Production Std = SUMX ( SUMMARIZE ( Table1, Table1[Job Num], Table1[Asm Seq], Table1[Op Seq], Table1[Op Code] ), CALCULATE ( MAX ( Table1[Production Std] ) ) )
Measure Labour Hrs = SUM ( Table1[Labour Hrs] )
Measure Efficiency = DIVIDE ( [Measure Production Std], [Measure Labour Hrs] )
You may add the following measures.
Measure Production Std = SUMX ( SUMMARIZE ( Table1, Table1[Job Num], Table1[Asm Seq], Table1[Op Seq], Table1[Op Code] ), CALCULATE ( MAX ( Table1[Production Std] ) ) )
Measure Labour Hrs = SUM ( Table1[Labour Hrs] )
Measure Efficiency = DIVIDE ( [Measure Production Std], [Measure Labour Hrs] )
Hi Sam
Brilliant that worked perfectly thank you for your prompt help.
I'm loving Power Bi.
Regards
Richard
Go to the Query Editor, select the Job Num, Asm Seq, Op Seq, and Op Code columns, and hit the Group By button.
Then in the Aggregations area use the following columns and operations:
Production Std - Min
Labour Hours - Sum
Required Qty - Sum
Completed Qty - Sum
That will get you the table you're looking for. Then you can just divide production std by labour hours to get your efficiency.
Let me know if you need any further help.
Hi SSugar
That definitely helped me understand Power Bi more.
I could group the data and that would fix my problem, however, the measure suggested below would allow me to drill down.
Thanks for your help.
Richard
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |