cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

MTBF calculation different structure of data

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:

 

mtbf.jpg

 

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 ModelMachineShiftIDShiftIDDateReasonDescriptionShiftDurationEventCountBreakdownDuration
a67529349195308/08/2019 0:001_a_504801856
a67529349195308/08/2019 0:002_b_73480199
a67529349195308/08/2019 0:003_c_33480193
a67529349195308/08/2019 0:004_d_23480271
a67529349195308/08/2019 0:005_e_6048031054
a67529349195308/08/2019 0:006_f_36480141
a67529349195308/08/2019 0:007_g_89480132
a67529349195308/08/2019 0:008_h_284806645

 

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: MTBF calculation different structure of data

Hi @tyr ,

 

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.
 
Annotation 2020-02-17 151724.png
 
 

View solution in original post

1 REPLY 1
Highlighted
Resolver I
Resolver I

Re: MTBF calculation different structure of data

Hi @tyr ,

 

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.
 
Annotation 2020-02-17 151724.png
 
 

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors