MTBF calculation different structure of data

[Anonymous]

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.

https://gofile.io/?c=3fc0Uw

Any comment would be helpful.
Thanks a lot!

[Anonymous · Accepted solution]

Hi @Anonymous ,

I've had a play around with your PBIX file and think I've managed it. Take a look here:

https://gofile.io/?c=cUMRAu

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: 

Total Uptime = IF( SUM( data[ShiftDuration]) = 0 , 0 , CALCULATE( AVERAGE( data[ShiftDuration] ) - SUM( data[BreakdownDuration-Minutes] ) ))

To get the MTBF I used:

MTBF = IF( SUM(data[ShiftDuration]) = 0 , 0 , DIVIDE([Total Uptime], SUM( data[EventCount] ) , 0 ))

Hope this helps.