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

# LOD for Top Line Result

Hi,

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

Data:

Tableau Workbook:

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.

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

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

Topline

- The desired result: 7.03

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

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

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

3. Score

- Score Index * CRM Score

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.

Thanks,

Wayne.

• ###### 1. Re: LOD for Top Line Result

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

Hi Shinichiro,

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

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

Excel Screenshot:

Thanks,

Wayne.

• ###### 3. Re: LOD for Top Line Result

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.

Thanks,

Wayne.