6 Replies Latest reply on Sep 17, 2018 6:57 AM by Alyssa Ashton

    Top and bottom percent in same visual

    Alyssa Ashton

      I am trying to get the top and bottom percent in the same bar graph.  For example, total sales by salesperson, user picks a percentage, shows top N% and bottom N% based on total sales.  I've found tutorials online and I've been able to get top percent alone and top and bottom together but by number(not percent, for example top 5 salesmen) using Sets.

        • 1. Re: Top and bottom percent in same visual
          Joe Oppelt

          What do you mean by top 5%?  (For example.)  Would that mean, if you have 1000 rows, the top 5% would be the top 50 sales guys?

           

          Hack up a workbook using superstore that does n% the way you want it done.  I'll show you how to add in the bottom n% using the same logic at the bottom of the list and display both together.

          • 2. Re: Top and bottom percent in same visual
            Alyssa Ashton

            Yes, that's right.  Thanks Joe, I will do that.

            • 3. Re: Top and bottom percent in same visual
              Alyssa Ashton

              Hi Joe,

              Here it is, worksheet Top N%.

               

              Thank you

              • 4. Re: Top and bottom percent in same visual
                Joe Oppelt

                (V10.5 here)

                 

                So the example uses a calc called Total_Customers.  That tells us how many customers are in play here.  (I would have calc'd it a different way, but there are often multiple ways to arrive at a number in tableau!)  I added that number to the title of the sheet.

                 

                10% of that number should be 79, and we can see that in the original TopN sheet we get 79 rows.  So we know that logic is right.

                 

                Now I want to add to this viz the bottom 79 guys.

                 

                I made a copy of the sheet so that we don't lose what has already been done.  I'll do the rest of the work in the new sheet.


                I also made a copy of the Top N% filter calc.  Take a look at that.

                 

                I added this to the original:

                 

                OR

                INDEX() > [Total_Customers] - ([Total_Customers] * [Top N%] / 100)

                 

                 

                The INDEX() that is being used here is the 1-through-N of all the rows in the table for this sheet.  [Customer Name] is sorted by SUM(profit).  So row 1 is the top profit guy.  Row 2 is the next highest, etc.  [Total Customers] is the total number of guys overall.  So guy# 793 is the worst profit. And so forth from the bottom up.  My logic says, "go backward from the bottom, the same number as we counted down from the top.  Get everything from there to the end."  Because I added it to the original logic with "OR", it will evaluate as TRUE for the top 79, and for just as many from the bottom up.

                • 5. Re: Top and bottom percent in same visual
                  Alyssa Ashton

                  Thank you Joe for your time and explanation, I got it working!!! 

                  • 6. Re: Top and bottom percent in same visual
                    Alyssa Ashton

                    I am trying to take this a step further.  If I were to add a Region filter, to only choose N% of customers by a region.  Add this to a dashboard.  Bottom half of dashboard I would like to display a bar graph of the products this N% is purchasing (as a group, not by each individual customer).  I've got the product worksheet to display product quantities by customer by region, but it is showing all for that region, not just the N% chosen, and I do not want to group it by customer but by the group as a whole.  Any ideas?