I have a table calculating MTBF for various machine types, with a chart looking like this:
The machine types all fall under certain machine brands. I want users of this chart to be able to view the MTBF at either a brand level, or a type level. At the moment, the MACHINE BRAND slicer only affects the Moving average line, not the bars for MTBF. I can only get the MTBF bars to show if just a single MACHINE TYPE is selected.
I do have MACHINE BRAND information available, as per the table view below, but the MTBF calculation is based on MACHINE TYPE.
The Moving Average is calulated like this:
Moving Average MTBF = AVERAGEX(FILTER(BaseSummary,[Machine Type]=EARLIER(BaseSummary[Machine Type])&&[Month/Year]<=EARLIER(BaseSummary[Month/Year])),[MTBF])
and the MTBF is calculated 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”
)
)
Total Soft Repairs =
var _Type = SELECTEDVALUE(‘BaseSummary'[MonthYearMachineType])
RETURN
COUNTROWS(
FILTER(
‘WO Raw Data’,
‘WO Raw Data'[MonthYearMachineType] = _Type
&&
‘WO Raw Data'[HARDSOFT] = “Soft”
&&
‘WO Raw Data'[MTBF Repair?] = “Repair”
)
)
TOTAL REPAIRS = ‘BaseSummary'[Total Hard Repairs] + ‘BaseSummary'[Total Soft Repairs]
MTBF = DIVIDE(SELECTEDVALUE(BaseSummary[OperationalWeeks]), ‘BaseSummary'[TOTAL REPAIRS])
I have a separate table called ‘Brand and Type’ which lists all the machine types and their corresponding brand, and the brand is also available in the WO Raw Data table that the above calculations use to count rows of data,…but I’m unsure how to use this to affect the MTBF bar chart.
Please can someone help?