5 Replies Latest reply on Oct 29, 2015 10:28 AM by Munish Gupta

    How to combine top N filters with % of totals calculation?

    Munish Gupta

      I am about 20 EXPENSE Types. Each Expense type has a amount with it. The data is for 3 months but will grow. I am looking to create a bar chart showing the top N expense types by amount and each of the N expenses contribution as a % of total.

       

      The problem is when I change the TOP N from 3 to 5 for e.g, my % of totals changes as well. I have looked around and found 3 methods

      1) Duplicating data

      2) Creating the calculation using LOOKUP

      3) and RAWSQL

       

      But I am beginner in Tableau and could not understand and use any of the 3 methods to make it work. I think my problem is rather quite simple and the solution should be out there.

       

      Any help is appreciated.

       

      @

        • 1. Re: How to combine top N filters with % of totals calculation?
          Simon Runc

          hi Manish,

           

          As you've seen from your research there are several ways of doing this...attached is one method using index() to filter the Top N.

           

          This is the same 'theory' as the LOOKUP solution, but a bit simpler to follow. The reason it works, is due to Tableau's 'Order of Filter Operations'

           

          Order of Operations AM.png

           

          Index() is a Table Calculation, and as we're using this to filter (and Table Calc Filters are applied last) this has the affect of Filtering the View, but not the underlying data, so any %age of Total are still correct.

           

          in the attached I've brought index() into the view so you can see what it's doing, but any final solution will only need the filter part. So the key here to sort the bars by the measure (in this example sales). This way by running the index() function 'Table Down' it acts like a Ranking function (you can set this up in the Table Calc itself, but it's easier to sort the view). I've then created a Boolean filter (based on a parameter I've created, so the user can choose their N)

           

          'Show Top N Filter'

          index() <= [Show Top N]

           

          I hope this makes sense, and solves your issue, but please post back if not (there are a few other ways to go, if this one doesn't do the job)

          • 2. Re: How to combine top N filters with % of totals calculation?
            Munish Gupta

            Hi Simon,

             

            Thank you for responding. I do not see any attachment. Could you please

            re-send?

            How do I create a table calculation? As you can see, I am still a beginner.

             

            Regards

            Munish

             

            On Wed, Oct 28, 2015 at 3:00 AM, Simon Runc <

            • 3. Re: How to combine top N filters with % of totals calculation?
              Simon Runc

              hi Munish,

               

              Can you not see this?...Let me know, if not, and I'll try and re-attach it. But you should be able to just click on the attachment and it should download

               

              Attachment.png

               

              Table Calculations are just a particular type of calculation. So by using the function index() it is a Table Calculation. Tableau does have 'Quick Table Calculation', but all this is doing is building you a formula (for the most common use-cases - such as Percentage Difference, Percent of total...etc.) using the various Table Calculation functions.

               

              I'd advise starting with the on-demand videos. In the Calculations section there are some on Table Calculations to get you familiar with them and the concept. After that I'd then watch Jonathan Drummey's excellent TDT video

              TDT with Jonathan Drummey - July 10, 2014

               

              Hope that helps, but please post back if you can't get the attachment.

              • 4. Re: How to combine top N filters with % of totals calculation?
                garima.vatsa.0

                Hi Munish,

                 

                I have attached a sample workbook for your reference to calculate Top N with % of Total Calc. Hope this helps.

                • 5. Re: How to combine top N filters with % of totals calculation?
                  Munish Gupta

                  Simon, Thank you so much.  I am able to see the attachment. With your help,

                  I am able to get it to work.

                   

                  Regards

                   

                  On Wed, Oct 28, 2015 at 10:36 AM, Simon Runc <