Sorting the measures is an important step in building a vizualisation as it helps us digest the data on display quicker. Nested sorting is required when there is more than one dimension in the view.
I’m using superstore data set for this exercise, I want to find the top 10 product that generate the most profit in each region. To build the visualisation I dragged the Region and Product Name to Rows in that order and Profit to columns. I dragged Product Name to Filters to get the top 10 products.
As you can see in the figure above, we are not showing the top 10 product names in each region. To fix this we are going to use the rank table calculation.
Drag Profit to Detail on Marks card, right click to add a Quick Table Calculation and select Rank. Right click again to select Edit Table Calculation, in the Table Calculation window that pops up select Pane (down).
Right click Profit in Marks cards and convert it to discrete, the pill colour will change from green to blue.
Drag this blue pill to Rows and drop it between Region and Product Name. You should see a header for Rank appear in the view.
I removed the product name filter that I had in the view before and pressed control and dragged the discrete (blue) Profit pill in rows to Filters.
Select 1 -10 for top 10 and click ok. You will now see the top 10 product names per region. Clean up the view by right clicking on the discrete (blue) profit pill in rows and untick show header.
Right click and select format to clean up the row and column divider lines.
I use nested sorting quite often in my visualisations. I hope this is useful.
