Summarising and Averaging Measure Not Giving Desired Result

[CMoppet]

Hello,

I have a table (sample data below) that calculates MTBF for MACHINE TYPE by dividing the value in column G by the value in column J for each row:

I have added a bar chart with a slicer for MACHINE TYPE and it all works fine if you select a single MACHINE TYPE in the slicer.

But…I also want the user to be able to slice the bar chart at a Machine Brand level (column D), rather than just for individual Machine Types. 

I’ve added a slicer for ‘Machine Brand’ but it doesn’t act as I need it to…it is currently calculating the average of the values down column K of my table. 

What I actually want it to do is give the result highlighted in yellow, which is based on (the sum of column G for Machine Types within the same MACHINE Brand) / (the sum of ALL Repairs for Machine Types within the same Machine Brand)

To demonstrate, in the bar chart below, filtered to show Machine Brand ‘AA’, you can see that August 2023 shows an MTBF of 232 because it is averaging cells K3:K10.  It should actually show a result of 66 (the sum of G3:G11 / the sum of J3:J11).   Similarly, September 2023 shows 250, but should be 67.

Do I need a new measure? Possibly an aggregation measure?  If so, please can someone explain how to do this?  

Many Thanks

[CMoppet]

I'm going to mark this as complete and log a new topic where I try to explain it a bit more clearly...I think I've overcmplicated my request in this one.

[amitchandak · Accepted solution]

@CMoppet , for Avg, make sure you use a measure

Avg = Divide(Sum(Table[Column G]), Sum(Table[Column J]) )

Over all Avg = calculate(Divide(Sum(Table[Column G]), Sum(Table[Column J]) )  , allselected())

[CMoppet]

@amitchandak 

I've created the two measures, as per below:

Avg = DIVIDE(SUM(BaseSummary[OperationalWeeks]),[TOTAL REPAIRS])

Overall Avg = CALCULATE(DIVIDE(SUM(BaseSummary[OperationalWeeks]),[TOTAL REPAIRS]),ALLSELECTED())

Unfortunately, I'm still not getting what I need, as it still isn't providing brand-level MTBF.

How can I write a measure that sums all the Operational Weeks for a specific brand, and then divides by the sum of all the Total Repairs for that brand?

It feels like it should be simple and I'm just overcomplicating it!