Occupancy of machinelines related to labour hours per specific day

[Anonymous]

Hello Power BI users,

I would like to calculate the machineline hours from the number of hours worked by employees for a specific day. However, when 3 or 4 employees work on the same machineline you cannot sum the hours of the employees toghether, because a machineline is not depended on the number of employees. The right calculation would be to look to the first employees labour time (FromHour) considering the same day where the last employee (To hour). But some machinelines do also stop working during the day. Thus a machine can work only max for 24 hours in a day. How am i going to group to apply this? My end calculation for this machineline should be around ~248 hours in total and not 1271. Power BI report and the excel sheet is in the attachment.

Excel
https://1drv.ms/x/s!ApwLzcPKGlcdjU-kjZ78nRMOjq4u?e=BmM9lE

Power BI
https://1drv.ms/u/s!ApwLzcPKGlcdjVDe8z2rQjL9Y3Rh?e=zw3d0R

Thank you in advance for helping me.

[v-henryk-mstf]

Hi @Anonymous ,

It doesn't seem to be very clear about your needs. What kind of results do you want to achieve?

For example, for May 8, 2018, do you want to get the machine's start time to end time, and then display it in the card visual or line?

Best Regards,
Henry

[Anonymous]

Hello Henry,

yes i would like to sum up the hours of machine start time to end time and than display it in a matrix/table (See below). This table should sum up all the machine hours for the machineline toghether like this for only the yellow marked rows you have drawn in your example. Than relate the hours to the startdate of that machineline. Like below.

MachineLine    Hours    FromHour
219                   13.75     8-5-2018

[Anonymous]

Hi Henry,

I want to consider hourly worked on a machine as machinehour. Since, there are for example 4 machines in a row we call that a machineline. Hence, adding all the hours of the labour hours is wrong, because it doesnt matter whether there are 10 employees working or 1, the machine is still working. Thus, in your example i only want to see for this order employee no: 11191 and 14383. Thus in total 13.75 machineline hours is the right answer. I hope you understand my difficult question/need. Thank you in advance.

Yours sincerely,

Baris 

[aj1973]

Hi @Anonymous 

is this looking close to what you are looking for?

However I don't see how do want to get the 248 hours in total!!

[Anonymous]

No this is too simple for my solution. For example the first one Tuesday, May 08, 2018 i only want to see the hours of the early shift and the latest shift, thus the hours of the employee numbers of 11191 or 12612 and the latest employee 14300. Thus in total 7,5 hours of the first employee plus latest employee 6,25 hours. That are in total 13,75 hours of machineline hours. Because it doesnt matter whether there is working 1 employee on the machineline or 100 employees on the same machineline. The machineline is still running. What more important for me is to know from what time to what time was the machineline running. If you know this solution. Are you able the share the power BI report with me?

Thank you in advance for your next reply.