8 Replies Latest reply on Jun 12, 2015 3:03 PM by Benjamin Meek

    Dynamic Sort Top N with Grand Totals and Subtotals

    Mahesh Devireddy

      Hi All,
      Hope you all had or having a great weekend. Here is another seemingly simple requirement, which looks complicated in Tableau. I am attaching a simple sample workbook, I have Region and Item and I am looking for Top N items by sales.
      I was able to accomplish dynamic sorting based on the measure you select and sort order with various other filters. The problem is when I want to check the Totals and Subtotals. Because of the way Tableau calculates Totals and Subtotals the Subtotals are for entire Items in the region not the Top 3 or Top 4 that I select, since filters do not apply to Table Calculations.

       

      1. http://www.clearlyandsimply.com/clearly_and_simply/page/2/
      2. http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/


      The article 1 helped me build a robust Dashboard with Dynamic sorting where I can select the Measure to sort by and the Sort Order Ascending or descending and various other parameters. (Check the screen shot).

      If you think about the ask it’s simple, I want to show Top 3 or Top 5 or Top N Items and have the subtotals and Totals for only those items that show on worksheet.

      Another option I tried is Using Top N, it does not work as Top N shows for overall for the Dataset and not broken by Industry or Region in the attached workbook. Check this post using Top N but if you look at the items same items are displayed and not exactly in descending order by sales.

      http://community.tableau.com/thread/135965

      I tried to implement Jonathan Drummy’s recommendation as in Article 2 above, but the moment I add Index to get Top N it breaks too.

      Having spent considerable amount of time over the weekend I decided to reach out to you all. May be it is some simple tweak that I am missing. I am hoping someone has that simple tweak to help me out.

      I tried several options, like using Rank instead of Index, Top N, Total instead of Windows_Sum. Asl tried to use custom SQL like Select TOP 5 column1, column2, sum(measure1), sum(meaasure2) with order by and group by clauses hoping to insert parameters for N and order by group by etc, however Tableau comes back with a error stating sub queries cannot be used. For it to work I now have to use OPENQUERY which requires a linked server set up. Which seems too much to ask on my current project. We are using SybaseIQ FYI.

      Thought of using two worksheets and format them in Dashboard, but it won't work since my Top N will change.

       

      Looks like there is a small basic thing I am missing to make it work. I may be complicating it.
      Really appreciate any help or direction.

       

      Thanks

      Mahesh Devireddy