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
The thread that you linked to is very old, and you may have noticed that in the thread, it links to another thread with a better alternative: http://community.tableau.com/message/137933#137933
https://public.tableau.com/views/NestedTopNwithOtherjmedit/Sheet7 is that approach modified for your situation, with some additional capability.
Notice that with an Advanced Compute Using and Restarting Every configured, we can use WINDOW_SUM(SUM([Sales]),0,LAST()) to get the sum of the others. I think this is a nice use case for using the arguments of the WINDOW_ function.
If you want the label to move to the outside of the others bar, you can turn stacked marks off.
Emma, how do you then compute Sum of Sales of your TOP N as % of your Sum of Sales of ALL?
Hi I am trying to check if its possible to get top n customers for each top n categories
This was useful, I really appreciate you having shared this, Emma.
What is sales for others field as it is not explained above
Divya, I believe it is this:
IF RANK(SUM([Sales])) <= [Top N]
THEN 0
ELSE [Sales]
END
Sales for Other
IF RANK(SUM([Sales])) > [Top N] THEN SUM([Sales]) END
I have a similar issue where I wanted to show accounts receivable by top N clients and others (all other clients more than N are summed) and show this separately for each of our business groups of which there are 8. I think this approach will work so thank you Emma! However suppose I turn the bars into stacked bars with color coding based on buckets of number of days the accounts have been outstanding, will that work or will it affect the rankings within each one of the groups?
Thx
Craig
I came across this video on youtube and they explain how to do this pretty well. https://www.youtube.com/watch?v=AmlMW6thpd4
Hi Emma,
Remove the duplication of the “Others” logic is not working since sometime the no. of ranks appear are less than the N value selected.
e.g. In my case N = 10, after 5 values , rest (more than 100 values) are of same values.
Then when I filter RANK(SUM([Sales]))< 12, still multiple 'Others' appear 7 times instead of once.
Appreciate if you can resolve this.