Forum Discussion

CMoppet's avatar
CMoppet
Icon for Helper IV rankHelper IV
2 years ago
Solved

Create an MTBF Measure that summarises Machine Types by Brand

Hello,

I'm quite new to PBI and have been stuck on a task since last week.  I'd be so grateful if someone can help me, please....It's driving me crazy!  My firewall prevents me from uploading a file, so I've tried to recreate the table to demonstrate.

 

I have a table called 'BaseSummary' which includes a count of each machine type that is active each month, the brand it belongs to, the number of repairs for each machine type in that month, and a column for the MTBF for the different machine types in that month.

 

 

How can I create a measure that gives me an MTBF by brand, rather than by machine type?  When I plot this on a bar chart, I want each bar to represent the brand, not the individual machine types.

 

To give an example, for August 2023, Machine Brand 'AA' includes 4 different machine types; 11,22,33 and 44.  I want to add up all the 'OperationalWeeks' for those four rows, and then divide by the sum of ALL REPAIRS across the same 4 rows.  This would then give me the MTBF for Machine Brand AA in August 2023, which should be 65.  Similarly, Brand BB for August 2023 would be 69.

 

I'd be so grateful if someone could assist.  I've tried a few posts to get a solution but think I'm not explaining what I'm looking for in a succinct manner!

Thank you

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    CMoppet Place Machine Brand into a Table visualization. Then create this measure and also place it into the table visualization. 

    Measure = DIVIDE( SUM( 'Table'[OperationalWeeks] ), SUM( 'Table'[ALL Repairs] ) )

    Also, you might find this helpful. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
    The basic pattern is:
    Column = 
      VAR __Current = [Value]
      VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

      VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
    RETURN
     ( __Current - __Previous ) * 1.

    • CMoppet's avatar
      CMoppet
      Icon for Helper IV rankHelper IV
      2 years ago

      Greg_Deckler   Appreciate you helping 🙂

      I've done the above but it gives me a blank table as soon as I add the new measure.:

      The Operational Weeks is this measure:  

      Operational Weeks = SUM(BaseSummary[Grand Total])*sum(BaseSummary[Weeks in Reporting Month])
      And TOTAL REPAIRS is this:
      TOTAL REPAIRS = 'BaseSummary'[Total Hard Repairs] + 'BaseSummary'[Total Soft Repairs]
      which comprises measures for both Hard ans Soft repairs, like this:
      Total Hard Repairs =
      var _Type = SELECTEDVALUE('BaseSummary'[MonthYearMachineType])
      RETURN
      COUNTROWS(
          FILTER(
              'WO Raw Data',
              'WO Raw Data'[MonthYearMachineType] = _Type
              &&
              'WO Raw Data'[HARDSOFT] = "Hard"
              &&
              'WO Raw Data'[MTBF Repair?] = "Repair"
          )
      )
       
      I just can't figure out what I'm doing wrong?