10 Replies Latest reply on Jun 20, 2018 6:40 PM by Yadan Wei

    How to fix calculation result ?

    Yadan Wei

      When I work on a data source like this, Please note that the relationship between Account and Strategy is many to many.

       

      StrategyAccountFee Ratio(BPS)
      S1A170.02
      S1A250.81
      S1A360.58
      S2A170.02
      S2A490.23
      S2A250.81

       

      Clients requires a average fee ratio for each strategy to compare, for example

      Average fee ratio (S1)=(A1 Fee ratio +A2 Fee ratio +A3 Fee ratio)/ 3(the account number of S1 strategy)

       

      And considering the many to many relationship I write a calculated field like this

      { FIXED [Strategy]: AVG({FIXED [Strategy], [Account]: [ Fee Ratio(BPS)]})}

       

      But this calculation has a problem, when I use account as a filter, the average will change with the account number selected.

      Ideally, this number should not change with any account selection.

       

      Could you tell me how to write this calculation so that the average data can be fixed.

      My apology for company security policy that restrict upload workbook.