Forum Discussion
Labour Efficiency
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
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] )
4 Replies
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
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
