6 Replies Latest reply on Nov 6, 2018 2:50 PM by Ombir Rathee

    Filter to show top N% of dataset, count of items

    Drew H

      Hi all,

       

      I have a dataset below/attached, and I want to create a filter to show me just the Brands that account for the top 80% of the set as well as a count of # of brands in the set.

       

      So for this example, the total amount is 592, so I would want to somehow want to:

       

      1. Get a list of which brands comprise the Top 80%, aka Brands X, I, J, G, Y and N and A (if you include partial counts for the top 80%).I assume this would be some sort of Rank + Sort?
      2. The count of how many brands comprise the Top 80%, in this case seven total

       

      I'm on Tableau 10.2 and any detailed instructions/assistance is greatly appreciated!

       

      Untitled.png

        • 1. Re: Filter to show top N% of dataset, count of items
          Jennifer VonHagel

          Hi Drew,

           

          I have the first part of your question, but am finding it surprisingly difficult to retrieve the count of your Top 80%.

           

          Here, at least, is how to filter for Top 80%:

           

          1. Create a Running Total %:

          Type RUNNING_SUM(SUM([Total])) / TOTAL(SUM([Total])) into the Calculation dialog box, then click on Default Table Calculation. We're going to set this up so the calculation knows to make the running sum order go along Brand for Sum(Total) descending.

           

          In the Table Calculation dialog box, choose Compute using: Advanced.

          In the Advanced Dialog Box, click Brand, then click the right arrow to put it in the "Addressing:" window. Under Sort, Choose "Field" and set it up to sort by the Sum of your Total metric descending. Click OK here, click OK in the Table Calculation box, and Click OK in the first Calculation dialog box.

           

          You can see the results here. Notice that even if we don't have the Total column sorted descending, the Total Running % column still calculates correctly.

           

          Now, we'll create a filter. Set this up exactly the same way as Total Running %, but add " < 0.81" on the end. This will create a True|False result. Remember to set up the Default Table Calculations the same as before.

           

           

          I'll double-click to add this to the table, color shelf, and as a filter.

           

          You would think that counting the number of items in the "True" bucket would be simple, but because the True bucket is created with a Table Calculation, it's not really a dimension. I would normally just count where that field = true, or maybe find the SIZE() of that table pane, but even the table calculations (Window_cnt, size) don't seem to recognize the difference between this field's true and false results.

           

          I hope someone else can chime in to solve this part of your request.

           

          Best,

          Jennifer

          2 of 2 people found this helpful
          • 2. Re: Filter to show top N% of dataset, count of items
            Ombir Rathee

            Like this ?

             

            1 of 1 people found this helpful
            • 3. Re: Filter to show top N% of dataset, count of items
              Drew H

              Thank you both, this has been helpful.

               

              Taking it one step further - if I wanted to have the count (7) and only that to be its own sheet (to place as a floating object on a dashboard) would that be possible?

              • 5. Re: Filter to show top N% of dataset, count of items
                Drew H

                That looks like what I need, yes! But I can't open the file you just posted (but could the prior one) - perhaps a versioning issue?

                 

                If you could post a different twbx version or even the formula for the "Last" calculated field I'd really appreciate it!

                • 6. Re: Filter to show top N% of dataset, count of items
                  Ombir Rathee

                  Downgraded to 10.2.

                   

                  If this helps then Please mark the answer correct to close the thread.

                  1 of 1 people found this helpful