Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sharksguts
Frequent Visitor

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 NumAsm SeqOp SeqOp CodeProduction StdLabour HrsRequired QtyCompleted Qty
1000101070000.50.341
1000111060110.80.766
10001210601121.888
1000101070000.50.443
1000102075002142
1000102075002142

 

Expected results:  Efficiency = Production Std / Labour Hrs

 

Job NumAsm SeqOperationOp CodeProduction StdLabour HrsCompleted QtyEfficiency
1000101070000.50.7471%
100010207500224100%
1000111060110.80.76114%
10001210601121.88111%
    5.35.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

 

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Sharksguts,

 

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] )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-chuncz-msft
Community Support
Community Support

@Sharksguts,

 

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] )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Sam

 

Brilliant that worked perfectly thank you for your prompt help.

 

I'm loving Power Bi.

 

Regards


Richard

ssugar
Resolver III
Resolver III

 

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

 

community-sol-565318.png


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.