Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I want to be able to establish the time between events for the same Work order and apply this delay to the next work center. Here is an example of the data and what I want to do :
Initial work table
Work order | Date | Hour | Work center | Operation |
123 | 2020-05-09 | 11:00 | A1 | 10 |
123 | 2020-05-10 | 13:00 | A2 | 20 |
123 | 2020-05-11 | 15:00 | A3 | 30 |
123 | 2020-05-12 | 17:00 | A1 | 40 |
456 | 2020-05-07 | 11:00 | A1 | 30 |
456 | 2020-05-10 | 13:00 | A2 | 40 |
456 | 2020-05-12 | 15:00 | A3 | 50 |
Here's what I want after
Work order | Date | Hour | Work center | Operation | Duration between | Next work center | Next operation |
123 | 2020-05-09 | 11:00 | A1 | 10 | 26:00 | A2 | 20 |
123 | 2020-05-10 | 13:00 | A2 | 20 | 26:00 | A3 | 30 |
123 | 2020-05-11 | 15:00 | A3 | 30 | 26:00 | A1 | 40 |
123 | 2020-05-12 | 17:00 | A1 | 40 | #N/A | #N/A | #N/A |
456 | 2020-05-07 | 11:00 | A1 | 30 | 74:00 | A2 | 40 |
456 | 2020-05-10 | 13:00 | A2 | 40 | 50:00 | A3 | 50 |
456 | 2020-05-12 | 15:00 | A3 | 50 | #N/A | #N/A | #N/A |
I'll then be able to evaluate the average delay of each part between each operation / work center
Part# | Next work center | Next operation | Average of Duration between |
A1S | A1 | 40 | 26:00 |
A1S | A2 | 20 | 50:00 |
A1S | A3 | 30 | 38:00 |
Total A1S | 40:24 | ||
Total | 40:24 |
I know I can do it in Excel, but can this be done with Power BI ? I have 1,200,000 rows of data to analyse.
I can easily get the min and the max to calculate the total duration of each work order, but I need to drill down to the workcenter.
Thanks!
Dominic
Hi @Anonymous
Please see the attached file with a solution.
Hi @Anonymous ,
You could refer to my sample for details. In addition, I don't understand the logic of second result table, could you please explain this to me?
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The second result table comes from the average of the 1st result table. For exemple, the first work order when to the work center A2 2 times and waited 26 hours the 1st time and 74 hours the 2nd time, which gives an average of 50 hours.
Also, I can't open your .pbix since I'm only using the Power Pivot complement in Excel.
Part# | Next work center | Next operation | Average of Duration between |
A1S | A1 | 40 | 26:00 |
Average A1 | 40 | 26:00 | |
A2 | 20 | 26:00 | |
A2 | 20 | 74:00 | |
Average A2 | 20 | 50:00 | |
A3 | 30 | 26:00 | |
A3 | 30 | 50:00 | |
Average A3 | 30 | 38:00 | |
Total A1S | 40:24 | ||
Total | 40:24 |
Dominic
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Thanks Greg, your MTBF calculation is really nice. I will surely use it.
If I understand correctly, it would gives me the average time after an event. In my case it's a little bit more complicated since I wanted to establish the time before the event.
I need to find out for each row to which work center the part is going in order to use your calculations. I can then make the average based on the "Next work center"
Regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |