Forum Discussion
PowerBI Desktop
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY5LCoAwDESvIlkLSWrqZ+nvFKX3v4YdqlUR6S5vHjMkBJqpJXEsyk60B3QFYhtoQeRZDZEAhgLwK6IpV5wlUGH1GeC33L996l8AvyOyp7dX//ufVf4bK/8p57v52feV/XMM+/EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Equipment = _t, CommissionedDate = _t, BreakdownDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CommissionedDate", type date}, {"BreakdownDate", type date}}),
process = (tbl)=>
let
#"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 then null else [BreakdownDate]-#"Added Index"[BreakdownDate]{[Index]-1}),
#"Grouped Rows1" = Table.Group(Table.SelectRows(#"Added Custom", each ([Custom] <> null)), {"Equipment"}, {{"Avg", each List.Average([Custom]), type duration}})
in
if Table.RowCount(tbl)=1 then null else #"Grouped Rows1"[Avg]{0},
#"Grouped Rows" = Table.Group(#"Changed Type", {"Equipment", "CommissionedDate"}, {{"Rows", each _, type table [BreakdownDate=nullable date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "MTBF", each process([Rows]),type duration),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"})
in
#"Removed Columns"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
Hello lbendlin
Can you please suggest this formula in PowerBI DAX instead of M-Query?
I don't understand M-Query that much. Plus, its a very hard programming language.
Thank you!
Hello lbendlin ,
I am not sure how are you getting MTBF for Equipment A as 30.5 days and Equipment B as 24.50 days. Because, Equipment A has failed 3 times and the total operating time is 30+61+91 = 182 days, No of times it has failed = 3, So, MTBF should be 60.67 for Equipment A.
Similarly, For Equipment B, the total operating time is 62+93+111 = 266 days and it also failed 3 times, so the MTBF for Equipment B should be 88.67 days.
Can you please tell me your logic here?
Thanks.
I guess you and I have a different understanding of what MTBF means.
Equipment A has failed three times. That means it had two "times between failure". MTBF can be computed by averaging each individual "time between failure" or - simpler - by dividing the time from the earliest failure to the latest failure by two, resulting in 30.5 . The "total operating time" has no influence on the MTBF calculation.
Hello lbendlin ,
This is very helpful! This algorithm is yielding correct results for the MTBF for individual Equipments when I am using the Stacked Column Chart like this:
But I am trying to get the MTBF for all the Equipments for each month- January, February, March, etc..If I put the MTBF on the Y-axis, BreakDate on the X-axis and Legend = Equipment, I get the MTBF for the individual equipments but not as a whole for all the Equipments.
If I add the MTBF for all those individual Equipments, I am getting MTBF like 150, 200 for respective months whereas I am expecting value anywhere between 10-20 days.
How do I get the MTBF for all those necessary Equipments for each respective months?
Thanks
Hello lbendlin ,
Adding to this question, I can neither write a Measure nor a Calculated Column inside of that table using Average Function for the MTBF33. Also, PowerBI also does not allow me to select SUM, AVERAGE, MIN, MAX dropdown from the MTBF33 shown in the visual above in the Y-axis.
I think applying average for the MTBF for all those individual equipments for each of the respective month will solve this problem but I don't know how to go about solving it?
Thanks