5 Replies Latest reply on Apr 9, 2012 6:18 AM by Jonathan Drummey

    Stop 'top N' filter affecting Percentage of Total

    jamesambler

      Hi,

       

      Have a request to show top customer by sales value, plus their % contribution to total sale for a given period / or region. In addition only the Top N number of customers are to be show.

       

      Whenever the Top N filter is applied the Percent of Total changes as the total used is a Windows Total. Have tried to replace the table calc with a calc/formula that I've written but this also affected by the Top N filter. Is there a way to have an absolute total that is unaffected?

       

      I can of course just enter the Total as an interger & run the % calc against that, but that would mean manually re-summing the total(s) everytime the data is refreshed. Surely there's a way to set 'absolute' amounts?? Having a bit of a brain freeze moment - any help would be greatly appreciated!

       

      Many thanks in advance.

        • 1. Re: Stop 'top N' filter affecting Percentage of Total
          Shawn Wallwork

          Jim, try duplicating your data source, then use it to pull your totals. If you post a sample, we could take a look.

           

          --Shawn

          • 2. Re: Stop 'top N' filter affecting Percentage of Total
            jamesambler

            Hi,

             

            thanks for your help & the suggestion - but didn't seem to work.

            Have attached a paired down & anonymised version.

            Looking at the tool-tip you'll see that the % changes as the parameter controlling top n is changed. have included the absolute total as well for reference.

            • 3. Re: Stop 'top N' filter affecting Percentage of Total
              Shawn Wallwork

              You're right that doesn't work in this situation. I worked on this for several hours and didn't come up with squat. I even saved out your data and then used an idea from a very old thread to use RAWSQLAGG call to try and get around the patrician, no go.  Maybe one of the other folks will have a go at it. If there is a way of doing this it certainly would be useful.

               

              Here's a link to that old thread that discussed a similar problem: http://community.tableau.com/thread/107508

               

              Sorry I couldn't help.

               

              --Shawn

              1 of 1 people found this helpful
              • 4. Re: Stop 'top N' filter affecting Percentage of Total
                jamesambler

                Hi Shawn,

                 

                thanks for the effort you've put into this & for the suggestion of the other thread - will check it out & see if that sheds any light.. Quite amazed this is such an issue - would have thought being able to set 'constants' / use adsolute totals would be a regular requirement.

                 

                Anyone else got any ideas?

                Thanks in advance!

                 

                Jim

                • 5. Re: Stop 'top N' filter affecting Percentage of Total
                  Jonathan Drummey

                  Hi Jim,

                   

                  I took a look at this. The issues I saw in your posted workbook where that your compute using was not correctly set for the Top N Filter, and there wasn't any sort on it. I also couldn't figure out why you had a manual sort on the Date_Name field, nor why the set existed in the first place. And the Absolute % calculation was [Orders]/[Absolute Total], so that's aggregating by row when we need to aggregate by Customer, where each customer has multiple records. I went back to your original post, where you'd written "Have a request to show top customer by sales value, plus their % contribution to total sale for a given period / or region. In addition only the Top N number of customers are to be show.", and I created something that hopefully shows that.

                   

                  Here's the super-short way to do this, takes about 45 seconds given that you already created the Top N filter and parameter. I mention the time to show that Tableau has some very powerful built-in ways of doing these kind of calculations, if you haven't checked out http://www.tableausoftware.com/table-calculations then it's worth doing so to learn more of what Tableau can do.

                   

                  1. Set a filter on Year(Date) to 2012. I skipped the rest of your filters because I couldn't make sense of them.

                  2. Drag Shipto Customer Name (I'll just call it Customer from here on out) onto the Rows Shelf.

                  3. Click on the blue Customer pill to set the default sort to Descending/Orders/Sum

                  4. Drag the Orders measure onto the Columns shelf. Tableau now draws a bar chart

                  5. Click on the green Orders pill and select Quick Table Calculation->Percent of Total.

                  6. Drag your "is in Top N?" calculation onto the Filters shelf, and select True.

                  7. Right-click on your Top N parameter and choose "Show Parameter Control".

                   

                  Here's the longer form that lets you validate the above. This is how I usually start working with table calcs, I learned this from Joe Mako:

                   

                  - Created a "Workout Crosstab" worksheet

                  - Started with a table showing the dimensions on Rows (just Customer) and Measure Names/Measure Values on Columns. This lets us work on partitioning and sorting and see all the working values.

                  - Set the sort on the blue Customer pill to Descending, Orders, Sum

                  - Created a TC Total Orders calc that is TOTAL(SUM([Orders])) just so I could see how many orders there were, and dragged that into the Measure Values shelf. The default table calc shows the correct value of 103,212, I also set it to compute using to Customer and that comes out to 103,212 as well.

                  - Created a % of Total Orders which is SUM([Orders])/TOTAL(SUM([Orders])), which is also exactly what Tableau creates when doing the Percent of Total - Quick Table Calculation. Set the compute using to Customer as well.

                  - Dragged the existing "is in Top N?" filter onto the Rows tab so I could see it, and then clicked on the pill to set Edit Table Calculation->Compute Using to Advanced... then Compute Using To Customer and the Order Along to Orders/Sum/Descending. This ensures that no matter what the user does to quick-sort the view, the INDEX() sort will be working properly. When you slide the Top N filter up and down, you will now see the correct values of True/False change.

                  - Now that we have a working sheet, duplicate the view, drag the TC % of Total Orders onto the Columns shelf, get rid of Measure Names, take Measure Values off the text shelf, drag Orders onto the Rows shelf, and there's a view that will sort.

                   

                  Let me know if this works for you!

                   

                  Jonathan