Showing a Nested Top N with “Other” in Tableau
One of my favourite parts of my job as a Tableau Consultant is receiving questions via our support desk which get the brain really ticking to find a solution. A question was sent the other day that a couple of colleagues and I collaborated on: how can I see Top N product sub categories, ranked, with all remaining sub categories shown as “Other”, also nested in customer segments?
Here is the result they were looking for:
This looked like quite a common scenario, being able to show the Top N items (it could be customers by profit, or states by sales, or sales managers by sales), within a category, with all remaining items grouped as “Other”. Should be straight forward?
I first thought about using a Set or a Group to solve this problem. However Groups are static lists of dimension members, so we can’t apply a Top N (but we could show “Others”). Sets would allow us to do a Top N, but not display the “Others”. A quick search on the Tableau Forums showed this great post started way back in 2010 – this was a good starting point, although the results in the post were not nested – so a few tweaks were to be made.
The first step is to begin sketching out in Tableau the structure of our final view.
I took Segment and Sub-Category to Rows (notice I have removed Sub-Category from its hierarchy so the drill down function is avoided)
Then I created a Parameter for my Top N
Next was to create a calculated field which would display the Sub-Category label if it was in the Top N, otherwise display “Other”:
I added the new calculated field to Rows after Sub-Category, and displayed my Parameter control. The compute using for the new calculated field is Table (Down).
Step 2 was to get our rows sorted in descending order of Sales, with Other always displayed at the bottom of each Segment.
This was done by creating a Rank on Sales:
I then placed this on my Rows between Segment and Sub-Category. The compute using is set to Sub-Category. This means the rank is worked out for each Sub-Category, restarting (or grouped by) the Segment.
Step 3 was to remove the duplication of the “Others”.
Again I created a new calculated field, “Show?”:
Place this on the filter shelf and select only “True” values. This will remove any rows where the rank is greater than the Top N parameter value + 2. Make sure the compute using is set to Sub-Category for the filter to work correctly.
Step 4 is to create a field which will display the Sum of Sales for the Top N members, and the total Sum of Sales for Others.
To do this we need to use a table calculation which will create the total for all the others:
Placing this on columns will create a bar chart, alternatively you could place it on text to display a table. The compute using for this is also by Sub-Category.
All that’s left now is to make our chart looks presentable! I hid the Rank and Sub-Category headers. Then placed Grouped Sales on text to label the bars and hid the axis labels. I also added a border to the bars to make them stand out. I formatted the borders of the Row Dividers to show only a divider per segment, rather than one for each row. Lastly I edited the tool-tip to give only the relevant information to the end user.
In the process of coming up with this answer the team and I came up with a few different solutions. As always with Tableau there is no one way to answer a question. If you have a different solution please feel free to comment!
The workbook is available to download from Tableau Public too: https://goo.gl/2yKYJd