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

    Running sum restart by rank grouping

    Chloe S

      sample_running_sum_2.bmp

      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
          Okechukwu Ossai

          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
            Chloe S

            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
              Okechukwu Ossai

              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
                Zhouyi Zhang

                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
                  Chloe S

                  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
                    Zhouyi Zhang

                    Hi, Chloe

                     

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

                     

                    ZZ