thanks for you reply. However I don't get it to work properly. When I expand country and region, nothing appears for the fourth item in the ranking. If I change it to the second (china) and expand, country changes to Mexico & Pakistan. The same happens in my data. So this does not work either unfortunately
Even though the index calc is set to calculate per Country / Region, USA and China and many others are under index 1, which I don't get.
Oh, now you tell me, you want drilling too! Post a packaged workbook with sample data and your viz set up the way you want it (described it) and I'll be happy to take another look at this.
Shawn - I'd been working on a reply on Friday but an overaggressive spam filter was eating it, I have a solution solves the drilling problem.
Norman - Table calculations and drilling often don't work very well together, depending on what you want there might be a solution using that route or not. In addition, you didn't specify the type of data source, size of your data, number of products, etc. so a table calculation filter may not be the best route to go in any case. That was my first choice (because it's often the easiest), I set up a version in the TC Filter worksheet in the attached but like Shawn's workbook it's not drillable. When we drill down a hierarchy, the expanded elements are automatically added to the partitioning of the table calculation and for this particular table calculation filter cause it to return a lot more results.
Here are some other options that would require a lot more effort: You could create a separate data source that returns the top countries, and use that blended source as a filter for Tableau, or to do something in the underlying query. Many data sources have a built-in rank function that could be performed in the data source and enable the rank to simply become another column in your data.
Finally, here's an option inside Tableau: In the attached (using Superstore Sales as a model), I set up three Sets to get the Top 1, 2, and 3 countries, then a calculated field that looks at to return the particular top country - see the three Top Country worksheets for examples. Each country is now completely drillable down to State/City/Postal Code, and each Product Category is drillable as well. This does require using a data source that can support Sets in calculated fields (Tableau data extract, etc.). Also, depending on your data volume, this may not be performant as it will cause Tableau to issue a number of subqueries (I understand that Oracle and MySQL can have problems here).
Finally, when you have separate worksheets, Tableau will have to issue separate queries for each worksheet and that can result in cache misses, making for slower performance. Using a single worksheet with all the data on it and letting the user choose the filter, may be your best bet.
Thank you so much Jonathan!
Your solutions were very very helpful!! I ended up putting the 5 best countries into one table which made everything a little easier, but still I appreciate your help in finding a solution!