Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

MTBF per equipment

Hi,

I have a  table of data where I have recorded the equipment (production line) available time in minutes and another table where I have the downtime of the equipment of the production line. The goal is to calculate the MTBF per equipment.

I've tried to do it according to the formula in the picture bellow (using a measure), but, althouth the result is correct for the production line, for the equipments in the chart(blue) is wrong.

I thought that by using the formula and then sliding the equipment names to the Axis, the calculation would be correct but it is showing lower values for the equipment MTBF than for the whole production line, which is wrong.

Can someone help?

Thanks in advance

(PS: I'm new to powerbi)

 

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Anonymous ,

     

    We can use the following steps to meet your requirement.

     

    There is a many-to-many relationship between two tables.

     

     

    The measure like this,

     

    MTBF Eq = 
var sum_ = CALCULATE(SUM(Available[Available time]),ALL(Breakdown))
var _Count = COUNT(Breakdown[Downtime])
return
sum_/60/_Count

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    In between can try like this

    divide(divide(sum([Avaiable Time]),60), count([downtime]))

     

    Add table name and correct column name as per need

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak 

      Thanks or your help.

      I tried with the formula you suplied but the result i got in the end was the same. 

       

      Below the raw data for the exercise

      Raw data: https://we.tl/t-Dl5soKb1aw

       

      What I am trying to accomplish is a bar graph view of the MTBF per equipment which should be calculated like this: Sum(Available time)/60/count(downtime), as one example, the result for Gas filler should be 116080,99/60/ 7= 276,38 hours

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    We can create a table and relationship to meet your requirement.

     

    1. Create a Date table using SUMMARIZE function.

     

    Date = SUMMARIZE('Available',Available[Date],"Total",SUM(Available[Available time]))

     

     

    2. Then we can create a both direction relationship between date table and Breakdown table.

     

     

    3. At last we can create a measure to get the result,

     

    MTBF Eq = 
var count_ = COUNT(Breakdown[Downtime])
var sum_ = CALCULATE(SUM('Date'[Total]))
return
sum_/60/count_

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared and what is the relationship between tables?

     

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-zhenbw-msft ,

      First of all thank you for your help. Regarding the result you reached unfortunatly is not correct, please find below the correct values we sould be achieving:

       
      EquipmentCounter of breaksMTBF EQ (hours)Available time (hours)
      Check weigher - product6322,41934,683333
      Gas filler 7276,4 
      Valve inserter10193,5 
      Water bath3644,9 
      Capper 3644,9 
      Product filler3644,9 
      Printers 5386,9 
      Shrink wrapper7276,4 
      Crimper 11934,7 
      Actuator machine11934,7 
      Central Electricity11934,7 

       

      What i was expecting was for powerbi to sum the Total available time and, for each downtime/machine it would take the total available time and divide it by the number of breaks each equipment had.

      • v-zhenbw-msft's avatar
        v-zhenbw-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        We can use the following steps to meet your requirement.

         

        There is a many-to-many relationship between two tables.

         

         

        The measure like this,

         

        MTBF Eq = 
var sum_ = CALCULATE(SUM(Available[Available time]),ALL(Breakdown))
var _Count = COUNT(Breakdown[Downtime])
return
sum_/60/_Count

         

         

        If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

        BTW, pbix as attached.

         

        Best regards,

         

        Community Support Team _ zhenbw

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.