6 Replies Latest reply on May 20, 2018 11:05 PM by Zhouyi Zhang

# Running sum restart by rank grouping

Hi - I want to sum up Team members sales Top 5 (rank 1 thru 5) and Top 10 (rank 6 thru 10).  Basically showing their total \$ and % sales contribution to the 1) team they belong vs. 2) overall teams.  Thanks.

• ###### 1. Re: Running sum restart by rank grouping

Hi Chloe,

One way to do this is using a combination of parameters and sets. However, the result can only be displayed one Team at a time. If this is not an issue for you, the solution below works. See attached workbook.

Step 1: Create parameter [Select Team]

Step 2: Create calculated field [Team Selection]

IF [Team] = [Select Team] THEN [Team] END

Step 3: Create calculated field [Sales Rep]

{FIXED [Team], [Salesrep]: MIN([Salesrep])}

You'll use this field later to create sets

Step 4: Create calculated field [Total Sales]

IF [Team] = [Select Team] THEN [Sales] END

Step 5: Create a set [Top 5]

Right click on the [Sales Rep] field and select create set

Step 6: Create another set [Top 10]

Right click on the [Sales Rep] field and select create set

Step 7: Create [Combined Set]

Select both [Top 5] and [Top 10] sets together. Right click and select Create Combined Set.

Step 8: Create calculated field [Rank Grouping]

{FIXED [Team], [Salesrep]: IF MIN([Combined Set]) THEN "Top 10" ELSE "Top 5" END }

Step 9: Create calculated field [Top N % of Total Sales (Member)]

SUM([Total Sales])/SUM({FIXED [Team]: SUM([Total Sales])})

Step 10: Create calculated field [Top N % of Total Sales (All Teams)]

SUM([Total Sales])/SUM({SUM([Sales])})

Hope this helps.

Ossai

Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 2. Re: Running sum restart by rank grouping

Hi Ossai,

Thank you for helping me with this question.  I'm sorry I did not make it more clearly.  Although I only listed Top 5 (1-5) and Top10 (6-10) records for each team, I actually have Top5, 10, 15, 20 and 25.  I need to make a bar chart of each Top N group contribution to total sales.  In my search I found something similar to what Zhou did and below is the discussion title.  However I need to add % of total sales for each Top N group.

Buckets wise sum for Products ( Rank | Index | Bins )

Thanks,

• ###### 3. Re: Running sum restart by rank grouping

Hi Chloe,

I've not looked much at the link you sent. From your response, I assume that you have calculated total sales for the groups and built the bar chart, but you are looking for a way to add % of total sales to each group. Have you attempted any calculation and what difficulty did you run into? Maybe if you show what you have done with sample dataset that closely reflects your actual data structure, then community members can jump in to see if there is a possible solution.

Ossai

• ###### 4. Re: Running sum restart by rank grouping

Hi, Chloe

Please find my solution attached and let me know if you have question.

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 5. Re: Running sum restart by rank grouping

Hi ZZ,

Your solution worked.  I need to the same done by quarter but I cannot seem to make it work. It will not let me add an updated Tableau file.  I will open a new discussion 'Top N with more dimension'.  I will mark this as correct answer.  Thanks again!

• ###### 6. Re: Running sum restart by rank grouping

Hi, Chloe

Can you also mark my answer in this thread as correct as well? Thx

ZZ