This should be straightforward but isn't - there could easily be an easier method than mine.
First, creating a ranking by a measure is easy - use index() and set the sort order of the table calculation to be explicitly whatever measure you want. This is shown in the second sheet where the field [rank] is set to order by the sum of sales descending (right click [rank] on the shelf, edit table calculation and use the drop down to select advanced to see how this is set.
Creating the 'other' is also easy - just check the rank - see [Top 10 or other] in the third sheet. Because we need the product name to get the rank, it needs to be included on the level of detail.
Unfortunately because the product name is on the level of detail it causes the overlap on the 'others' - basically every remaining product sum of sales is overlapped.
Ideally we would create a window_sum that partitions using [Top 10 or other], but here lies the problem - you can't partition a table calculation by another. Instead we have to recreate the partitioning using another calculation [Sales All]
Then we're almost there (sheet Getting there), but Other, while showing the right value, still has overlapped text. We can solve this by showing only those ranked 1 to 11 (i.e. top 10 and Other). Note that the value for other does not get filtered because of the way filtering table calcs works in Tableau. To the corrected sheet, I've also added [rank] as the first column and hidden it so that the values are always sorted correctly.
Finally we can draw a pie chart. My preference would be a bar chart - pie charts with more than 4 or 5 slices are very difficult to read. Usually bar charts are better with fewer slices as well.
top n.twbx.zip 1.3 MB
1 of 1 people found this helpful
I am brand new to Tableau and your response to this question has been quite helpful. I have a follow up question, I hope you don't mind if I tag on to this conversation.
How would you go about adding labels that shows % of grand total to the pie chart?
I tried creating a measure PV% with the formula below. My thought is that I can use this as a label, but it does not display the correct %. This does not seem to work - see worksheet "label" in the attached workbook
THEN sum([Page Views])/TOTAL(SUM([Page Views])
THEN sum([Page Views])/TOTAL(SUM([Page Views])END)
I'm also having trouble uploading my workbook thus the snapshot. Please help.
I am looking for the same as Tom.
Did you find any way to get the percentages shown in the pie chart.
It is also very helpful and great if anyone wants to show the pie chart with top N values with others.
The use of dynamic set is useful but it does not work when a package is opened in reader, Can you think of any solution to this. I have been trying to get the resolution for this problem but nothing seems to workout in my favour.