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 everyone,
I am new in PowerBI and I think this question is tricky but hope it is not for experienced users.
I am trying to calculate MTBF(Mean Time Between Failures) but my data structure is different than all other examples.
There are several Machines, for each machine there are Shifts(3 in general) and I don't have detail of failure reasons.
MTBF formula is as below:
I calculate Sum(Average(ShiftDuration)) then minus Sum(BreakdownDuration/60) then divide to sum(EventCount)
If I select more shifts or days it needs to calculate in machine level. I can't calculate single MTBF for several machines.
Also there are some records where ShiftDuration is equal to zero and MTBF should be zero and it shouldn't effect other shifts as well.
Long story short my data looks like below:
Machine Model | MachineShiftID | ShiftID | Date | ReasonDescription | ShiftDuration | EventCount | BreakdownDuration |
a | 6752934 | 91953 | 08/08/2019 0:00 | 1_a_50 | 480 | 1 | 856 |
a | 6752934 | 91953 | 08/08/2019 0:00 | 2_b_73 | 480 | 1 | 99 |
a | 6752934 | 91953 | 08/08/2019 0:00 | 3_c_33 | 480 | 1 | 93 |
a | 6752934 | 91953 | 08/08/2019 0:00 | 4_d_23 | 480 | 2 | 71 |
a | 6752934 | 91953 | 08/08/2019 0:00 | 5_e_60 | 480 | 3 | 1054 |
a | 6752934 | 91953 | 08/08/2019 0:00 | 6_f_36 | 480 | 1 | 41 |
a | 6752934 | 91953 | 08/08/2019 0:00 | 7_g_89 | 480 | 1 | 32 |
a | 6752934 | 91953 | 08/08/2019 0:00 | 8_h_28 | 480 | 6 | 645 |
I calculated correct measures in excel(for only machine "a") and attached the file.
Also you can find sample data in .pbix file that includes the data as well.
Any comment would be helpful.
Thanks a lot!
Solved! Go to Solution.
Hi @Anonymous ,
I've had a play around with your PBIX file and think I've managed it. Take a look here:
First I added a custom column to get the Breakdown Duration in minutes:
BreakdownDuration-Minutes = data[BreakdownDuration] / 60
Ideally you should do that in Power Query but I didn't have access to do it here.
To get the Total Uptime I used:
Hi @Anonymous ,
I've had a play around with your PBIX file and think I've managed it. Take a look here:
First I added a custom column to get the Breakdown Duration in minutes:
BreakdownDuration-Minutes = data[BreakdownDuration] / 60
Ideally you should do that in Power Query but I didn't have access to do it here.
To get the Total Uptime I used:
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |