Bits of Tableau
Sort One-to-Many Dimensions Like a Pro in Tableau Dashboards
A Practical Way to Sort Nested Dimensions in Visualizations Based on Multiple Measures using Parameters
P.S. This article is a continuation of my first custom sorting tutorial.
In this read, I will share a tutorial on doing nested sorting on one-to-many dimensions in Tableau.
After following the tutorial, you’ll be able to make a visualization where you can choose the measure you want to sort the nested dimensions by and display only the Top N instances in the dashboard, as depicted below.
Alright, tutorial start!
Similar to the first tutorial, the dataset used is Tableau’s Superstore dataset, to visualize Superstore’s Top Performing Regional Managers and their Top 5 States based on 4 measures: Count of Orders, Quantity, Sales, and Profit Ratio.
Each Regional Manager can have many States, defining a One-to-Many Dimension, which makes this tutorial different from the first since we want to sort both the Regional Managers and their States based on the chosen Measure.
P.S. I used the same Sheet as the one from the first tutorial, hence, the created calculated fields in the screenshots. Do note that they will be re-introduced here.
- Drag the needed Measures to Columns and Dimensions to Rows.
2. Create a String Parameter to sort values by the Measures.
3. Create a @sort-by calculation to define the Parameters (make them work) — I like to use ‘@’ to differentiate parameter-defining calculations apart from other calculations.
4. Right-Click — Sort the Dimensions (Regional Manager & State), Sort By @sort-by calculation, and Sort Order Descending.
5. Here’s where it gets different.
To sort the States of each Regional Manager, drag the @sort-by calculation to Rows and change it to Discrete, which enables it to be changed to an aggregation, RANK.
6. Place the @sort-by blue pill between the Regional Manager and State pills, before changing the calculation computation to Pane (Down). Notice that the Ranking is reset based on each Regional Manager instead of the whole table. Yay! The sorting parts are now done!
Displaying all the States of each Regional Manager can be messy. To make it more flexible, a Top N filter can be made using the RANK calculation.
7. Duplicate and drag the @sort-by calculation to the filter card and change it to continuous.
8. Edit the filter, choose ‘Range of values’ to enable the slider display, and show the filter.
9. Rename the new slider filter to ‘Display Top N Performing State’ to show the filter’s purpose. You can now customize the display based on the Top N States you want to show.
10. Design the Dashboard and display all the Parameters and Filters.
11. Notice that the ‘Rank of @sort-by’ column name is not user-friendly enough. Since it cannot be directly renamed, there is a workaround by creating a floating Text object.
12. Change the background color to white to hide the original text.
13. Final dashboard look!
Woohoo! Sorting nested dimensions is not hard, right? Furthermore, you can customize the number of Top N instances to elevate the stakeholder’s readability of your dashboard.
For any inquiries, I can be contacted via LinkedIn. Hope this article can help you to make more impactful data-driven decisions by applying custom sorting parameters in your visualizations :).