SELECTED VALUE in a Custom Column?

[CMoppet]

Hello

I have attached a link to a test file to demonstrate a piece of work I am stuck on at the moment.    https://drive.google.com/file/d/1s5QikoceDHnJktFW0fZAAR8SBL26uOcu/view?usp=sharing

And this is the specific custom column that I'm stuck on...

MTBFTest = 
VAR MonthYear = [Month/Year]
VAR MachineBrand = [Machine Brand]
VAR MachineType = [Machine Type]
VAR Age = [Age Bracket]
VAR Market = [Market]
VAR _Results = SUMX(FILTER(ALL('RepsSummary'),'RepsSummary'[Age Bracket]=Age && [Machine Brand]=MachineBrand && [Machine Type]=MachineType && [Market]=Market && [Month/Year]=MonthYear),[Repairs1])
RETURN
IF([Repairs1]>0,DIVIDE([Operational Weeks in Reporting Month],[Repairs1]),[Operational Weeks in Reporting Month])

Please can someone help me adapt the custom column above so that the bar chart beneath it updates the results if just some of the variables outlined in the formula are selected?  For example, if someone chooses Machine Brand 'ABC' in a slicer and doesn't select anything from the other slicers, the result for August 2023 should be approximately 52.5, because I want it to disregard the Age, Market, Type and perform the DIVIDE calculation at just a Machine Brand level.  Similarly, users should be able to select just a Market and a Machine Type, and get the result for those disregarding the Age.

Is the problem with the way I've written the column?  How can I make the variables flexible rather than mandatory to get a correct result?

Many Thanks in advance

[CMoppet]

@shafiz_p  Following on from my post above, here's what I mean....

The value of 42 shown in the bar chart below is the average of the MTBF results 19 and 65 (highlighted.

However, what it should do in this scenario is add up all the August values in 'Sum of Total Active MCs', multiply this by 4.43 (weeks in period) and then divide by the sum of all the numbers in the August Repairs1 column.

[shafiz_p]

Hi @CMoppet  I think you had problem with the relationship between tables. Many to many relationship with both directional may lead to incorrect results. I have deleted all the relationship and used TreatAs function in the calculated column.

Updated Repairs1 Column formula:

Repairs1 =

VAR MonthYear = [Month/Year]

VAR MachineBrand = [Machine Brand]

VAR MachineType = [Machine Type]

VAR Age = [Age Bracket]

VAR Market = [Market]

VAR _Results =

CALCULATE(

    SUMX(FILTER(ALL('RepsSummary'),'RepsSummary'[Age Bracket]=Age && [Machine Brand]=MachineBrand && [Machine Type]=MachineType && [Market]=Market && [Month/Year]=MonthYear),[Total No. of Breakdowns]),

    TREATAS(VALUES(BaseSummary[Month/Year]), RepsSummary[Month/Year])

)

RETURN

_Results


Updated MTBFTest Column :

MTBFTest =

IFERROR(DIVIDE([Operational Weeks in Reporting Month],[Repairs1]),[Operational Weeks in Reporting Month])

After that I have found the updated Bar chart with Aug 2023 is 53.22, slightly higher value than you have mentioned.

Check this:

I think this is what you want!

Hope this helps!!
If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[CMoppet]

Ah! I think I know where the results are coning from!  When you add a slicer, it averages the results in the MTBFTest column, rather than performing the DIVIDE calculation again from scratch.  

So...if the user doesn't select any age bracket or machine type, it should simply calculate the total number of machines within the brand, across all markets, and the same for repairs, and use these in the DIVIDE calculation.  Does that make sense?

[CMoppet]

@shafiz_p   Hello 🙂  I have replcated your work and the result is now correct in the example of choosing a single brand; ABC.  However, if I test it again slicing by type and/or age, it doesn't seem to work for me.  For example, if you select Machine Type 'ABC Element', August 2023 should be circa 51.6 (4450 machines * 4.43 weeks, all divided by 382 repairs), and if you then also select the Age Bracket '1Yr1d - 5Yrs' the result for August should be circa 74.7. 

Please can you help me spot how to correct this?

[shafiz_p · Accepted solution]

Instead of column in y axis, Use measure. I have created this measure according to you calculation, you have mentioned in your replay.

Test =

DIVIDE([Operational Weeks in Reporting Month], SUM(BaseSummary[Repairs1]))

For 52.6, Check this:

For 51.6, Check the result (For same setting, no relation developed, only Base table used, bring all necessary values in Base table)

For Third case, I have manually filtered your table, and done manual calculation, it is not the same as you want. Why is this different?

For, Machine Type = ABC Element, Age Bracket = 1Yr1d-5Yr, Month/Year = Aug 2023, check this:

Tried manual calculation, and not getting your mentioned value which is 74.7 instead I found 63.6 and column chart showing me that value. Check the below image.

Hope this helps!!

[CMoppet]

Oh my goodness!  This works!!!  Thank you so, so much.  I can't tell you how long I've spent trying to fix this!    I'm so grateful 🙂