3 Replies Latest reply on Oct 2, 2016 1:19 AM by Wayne Ooi

    LOD for Top Line Result

    Wayne Ooi



      I am trying to use LOD to achieve below Score Results by:


      • Customer
      • Sales Person
      • Sales Manager
      • Top Line


      I could only achieve part of the "by Customer" result but having problems for the rest.


      Below are the details of the datasource and Tableau Workbook



      Screenshot 2016-10-02 12.43.09.png


      Tableau Workbook:

      Screenshot 2016-10-02 12.49.04.png

      by Cust

      I would like to show also the Grand Total Score: 7.03. I created a calculated field: Total Score = WINDOW_SUM([Score]) but I can only show it as Tool Tip.

      Screenshot 2016-10-02 12.49.45.png

      by Sales Person

      - Sum of Score of individual customers that belongs to that Sales Person

      - The desired result are:

      Sales Person 1: 0.89 + 0.39 + 0 = 1.28

      Sales Person 2: 2.22 + 1 + 0.28 = 3.5

      Sales Person 3: 0 + 0.33 + 0.07 = 0.4

      Sales Person 4: 0.8 + 0.56 + 0.5 = 1.86

      Screenshot 2016-10-02 12.50.40.png

      by Sales Manager

      - Sum of Score of individual customers that belongs to that Sales Manager or Sum of Sales Person's score that belongs to the Sales Manager

      - The desired result are:

      Sales Manager A: 1.28 + 3.5 = 4.78

      Sales Manager B: 0.4 + 1.86 = 2.26

      Screenshot 2016-10-02 12.51.56.png


      - The desired result: 7.03

      - i.e. sum of all individual score of each customers

      Screenshot 2016-10-02 13.19.44.png


      Below are the formula and criteria to calculate score:

      1. Score Volume

      - Score Volume is derived via Annual Volume, however, only if the CRM Score is more than 0 else it won't be considered

      Screenshot 2016-10-02 13.03.05.png


      2. Score Index

      - Score Volume / Total Score Volume

      - Total Score Volume is 9000

      - Score Index for customer C01 = 1000 / 9000


      - Total Score Volume is dynamic, depending on what user's filter; for e.g. user can filter to Sales Person 1, 2 and 3 then the Total Volume in this case will be only 5900 instead of 9000

      - Score Index for customer C01 = 1000 / 5900

      Screenshot 2016-10-02 13.04.01.png


      3. Score

      - Score Index * CRM Score

      Screenshot 2016-10-02 13.04.45.png


      I have tried LOD with INCLUDE and EXCLUDE as well but  still not able to achieve what I need. Really appreciate if someone could help.




        • 1. Re: LOD for Top Line Result
          Shinichiro Murakami

          You can simplify score calc.

          [SM Score2]

          [CRM_Score] * [Score Volume]/{fixed:sum([Score Volume])}


          1 of 1 people found this helpful
          • 2. Re: LOD for Top Line Result
            Wayne Ooi

            Hi Shinichiro,


            Thank you for your reply.

            I tried on the calculated field you suggested and they work fine if there is no filter applied to the dataset.


            I tried to change the suggested calculated filed so that they response to the filter but not successful.

            Would you be able to tell me please how I can change the calculated field so that it can response to the filter. For e.g. user can filter out certain Customers, Sales Persons or Sales manager.


            No Filter: Results are correct

            Screenshot 2016-10-02 20.26.17.png


            Filter out Customer C01: Results are not correct

            - Once data is filtered out, the score volume of that row should not be taken into consideration

            - Example in this case, the Score Index for C02 = 500 / 8000

            - Score for C02 = (500 / 8000) * 7 (please see last Excel Screenshot)

            - Total Score Index should always add up to 1


            Correct Results are:

            Top Line should be 6.91


            by Sales Manager:

            A: 4.38

            B: 2.54


            by Sales Person:

            1: 0.44

            2: 3.94

            3: 0.45

            4:  2.09


            by Customer: as per Excel screenshot

            Screenshot 2016-10-02 20.28.30.png


            Excel Screenshot:

            Screenshot 2016-10-02 20.41.34.png




            • 3. Re: LOD for Top Line Result
              Wayne Ooi

              Hi Shinichiro,


              I think I might have found the solution, I amended the suggested calculated fields and below are what I am doing:


              Step 1: Create Calculated Field: Score Volume

              {INCLUDE  [Customer] : SUM(IIF([CRM Score]>0,[Annual Volume],0))}


              Step 2: Create Calculated Field: Score Index

              [Score Volume] / {EXCLUDE  [Sales Manager], [Sales Person], [Customer]  :SUM([Score Volume])}


              Step 3: Create Calculated Filed: Score

              [CRM Score] * [Score Index]


              Thank you again for your help.