Forum Discussion

CMoppet's avatar
CMoppet
Helper IV
1 year ago
Solved

SELECTED VALUE in a Custom Column?

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 ...
Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    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!!

shafiz_p's avatar
shafiz_p
Super User
1 year ago

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's avatar
    CMoppet
    Helper IV
    1 year ago

    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's avatar
      shafiz_p
      Super User
      1 year ago

      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's avatar
        CMoppet
        Helper IV
        1 year ago

        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 🙂

  • CMoppet's avatar
    CMoppet
    Helper IV
    1 year ago

    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?