14 Replies Latest reply on Sep 14, 2017 11:41 PM by Subrakant Pradhan

# Sum of Top 10 Sales & % of total

Hi all,

I have been researching on this topic and found several approaches how to realize that Tableau only sums the Sales of Top 10 products. However, for some reason, I manage to get it to work but if I then change the filter (e.g. to a different Product Group or Subbrand), it is not correct anymore. Therefore, I have prepared a small sample workbook and would like to reach out to you with the following questions:

1. How can I make sure that Tableau sums the Sales of the Top 10 products displayed?

2. How can I make sure that this stays valid, if you change the filters, i.e. Subbrand

3. how can I calculate the Top 10 Sales as % of total sales for the week?

Thanks so much for your help.

Stephanie

• ###### 1. Re: Sum of Top 10 Sales & % of total

Good morning Stephanie,

Find my approach as reference below and stored in attached workbook version 10.2

1. Gross Sales Act Rank: RANK(SUM([Gross Sales Act]))

2. Drag Sales Act Rank to the right of Article ID on the Rows-shelf. Set it to Discrete. Drag it now between Subbrand & Article and set Compute using to Article ID

3. Filter Gross Sales Act Rank on 1 till 10

4. % of week: [Gross Sales Act]/{fixed [Fiscal Y/W]: sum([Gross Sales Act])}

Regards,

Norbert

• ###### 2. Re: Sum of Top 10 Sales & % of total

Hi Norbert,

Thanks so much for taking the effort. However, when I now change the filter on the right, Tableau does not sum the Top 10 but rather all products in the Subbrand: It shows 1.3 Mio EUR but it should be only 0.7 Mio EUR. Can you advice?

• ###### 3. Re: Sum of Top 10 Sales & % of total

Hi Stephanie,

Regards,

Norbert

• ###### 4. Re: Sum of Top 10 Sales & % of total

Hi,

Attaching the couple of screen shots of the calculations. Please do the same calculations to solve the problem. And let me know.

1. Create a Parameter.

2. Create a calculation.

3. Total Sales

4. Top N Total Sales.

5. Top N Sales %

6. Final View.

• ###### 5. Re: Sum of Top 10 Sales & % of total

Hi,

The last proposal works! This is fantastic!

I have one addition: How can I display the total sales (across all customers) and set the % in relation to that?

Thank you!

1 of 1 people found this helpful
• ###### 6. Re: Sum of Top 10 Sales & % of total

Hi,

Good to hear it works for you. Could u plz elaborate the "How can I display the total sales (across all customers) "

Waiting for your response. Also could u mark the above solution as helpful.

Thanks,

• ###### 7. Re: Sum of Top 10 Sales & % of total

Hi,

In your example, you have selected all categories (furniture, office, technology). The total sales is 2,2 Mio. If you now select furniture only, the total sales moves to 700k and Top 10 % is 7,9%. Is it possible to add another measure, that sets the Top 10 Sales of Furniture in relation to total Sales across furniture/ office/ technology (2,2 Mio)?

Hope this makes sense.

Thanks so much for help!

Stephanie

• ###### 8. Re: Sum of Top 10 Sales & % of total

Hi Stephanie,

1. First Calculation for the Total Sales irrespective of any filter

2. Second Calculation for the Top N Sales % irrespective of any filter.

3. Final View

Thanks.

Subra

1 of 1 people found this helpful
• ###### 9. Re: Sum of Top 10 Sales & % of total

Hi Subra,

Sorry to bother you again. I have integrated the measures as described. And it works. However, now the sales does not react to my changing time filter anymore, e.g. Total Sales (irrespective of any filter) stays the same, even if I change the Fiscal Week. Can you advice how to integrate the time in the calculation?

Thank you

• ###### 10. Re: Sum of Top 10 Sales & % of total

Hi Stephanie,

The LOD function which is used will not affect any filter selection. So instead of Fiscal Week Filter, you can create a parameter for the same and a calculation to make that parameter work.

Thanks.

• ###### 11. Re: Sum of Top 10 Sales & % of total

Hi Stephanie,

I got an idea, you can make that Fiscal Week filter as Context filter which will solve your problem.

Please find the below screenshot in which i make year as context filter. Please let me know.

• ###### 12. Re: Sum of Top 10 Sales & % of total

1 of 1 people found this helpful
• ###### 13. Re: Sum of Top 10 Sales & % of total

WAUW! Super!!!

• ###### 14. Re: Sum of Top 10 Sales & % of total

A Big Smile