You can accomplish this using the Rank() function and putting the sub-Category on the detail shelf instead of the column.
See in the attached on sheet 3.
From a duplicate of the sheet Using Rank;
-Drag the sub-Category on the Detail shelf
-Remove the Rank
-Set the computing of the filter , Compute using Sub-Category, keep True values
-As you see you get one mark per category on the row. You can hide the borders by going in Color, and set the same color in border
- If you want to show the Region Sales you can compute using
WINDOW_SUM( if [Rank] <= [Top N] then ( SUM([Sales]) ) end )
You need to reapply the Top N filtering localy in the calculation here, otherwise because you have a table calc in the Filters and the order of operation of
Tableau, the Window_Sum would be computed before the Top N filtering
-If you want to Sort desc, you can put a discrete value of the Negative value of the same Window_Sum at the left of the Rows and hide the values (unselect Show Header)
note: you could also put the Window_Sum on the label shelf, but to display the value on a single Region , sub-Category Mark , you would need to compute it using
if Rank = 1 then WINDOW_SUM( if [Rank] <= [Top N] then ( SUM([Sales]) ) end ) end
in order to display it only on the first mark.But then you will have alignment problems because you cannot control the sort order of the Category. And you could have overlapping problems if the
first category displayed is to small.
Nested Top N Workbook mc.twbx 455.3 KB
This is quite an elegant work-around that works well in situations when using a bar chart. I don't suppose you have another work-around up your sleeve for instances where a simple table of numbers would be used as the visualisation instead of a bar chart?
If I've understood correctly, the above won't work when using a table of numbers as having the sub-category on the LOD shelf would break those numbers up by sub-category on each row?
Sorry to be a pain ^_^
Thanks again for your help!
Ah! that makes sense.
Thanks very much for your help ^_^