Sorting in Tableau can be perplexing. Sometimes Tableau sorting appears to not work, it may not do as expected. Sometimes the sort doesn’t seem to sort at all, even when you tell it to!
Understand how Tableau sorting works, and remove some of the frustration it can cause!
Someone was complaining recently about the sorting not working in their Tableau table. Actually, the sorting was working, but they didn’t understand how to sort in Tableau. This post should alleviate some of this confusion and demonstrate how to make sorting work.
Tableau sorting basics
There are multiple different ways to sort in Tableau, with some taking precedence over others.
It’s possible to sort by a field, to create a manual sorts, to have alphabetical sorts and to have nested sorts. A field on the view can sort the table, as can a default sort setting, and sometimes there’s a sort icon appearing in the worksheet (but it doesn’t always do anything, and it doesn’t always appear!).
This article hopes to cut through some of the Tableau sorting confusion. It will explain different methods to sort your data and explain the impact of one sorting technique over another.
Tableau Sorts Dimensions
To begin, when sorting, Tableau sorts a dimension by something. That something can be the dimension values – i.e. alphabetical (which is the default if nothing else is specified), or some other business-specified sort order – e.g. North, then South, then East, etc.
Perhaps the most common sorting is via a measure, such as the sum of sales, the number of records, etc. Effectively, this is sorting the dimension by the measure.
When there are multiple dimensions in the view, this can become a Nested Sort. And these are almost always where sorting and confusion kicks in!
Sorting priority in Tableau
Think of your data as a simple table. Tableau sorts by the left hand column, whether it’s visible or not. In Tableau terms, this is the the first pill on the Row shelf, which is the primary sorting field. Understanding this helps define the sort, especially when using multiple dimensions in the view.
The first column in a table takes priority over any other sorting set on any other fields.
Remember the above sentence – it is very important to understanding sorting in Tableau!
To run through the basics of Tableau sorting, use a simple example, with one dimension and one measure.
This example uses the Tableau superstore data. Put sub-category to rows (i.e. the first column of a table!). Notice how it defaults to an alphabetic sort.
Setting the Default Sort of a dimension
The default sort order applies when there’s no additional sort applied to override the default. For example, if the default sort order is alphabetical, when dragged to the view the initial sort order will be alphabetical. But setting a specific sort within the worksheet overrides the default.
Set the default sort order by setting this in the dimension settings, which has a Sort option.
**Note – Altering the Default Sort order also changes the sort order when using as a filter**
Set a manual sort order, and see how the table alters, reflecting the new dimension default sort.
Sort by a measure
Most sorts in Tableau are sorting by a measure. A very common sorting use case is sorting by sales. To sort the table by the sum of sales, either:
- click the sort icon, which appears when hovering the mouse over the column header
- Alternatively, right click on the dimension pill, and set it to sort descending by the field.
Override the Default Sort
The default sort is what happens when first putting the field on the view. However, any sorting applied in the worksheet will override the default sort. For example, if the default sort order is alphabetic, sorting by the sum of sales in the view will mean the sorting by sales takes precedent.
More complex sorting – a nested sort
This is where the confusion begins with sorting in Tableau. If you think Tableau sorting is broken, or the sorting isn’t working properly, it’s almost certain to be a nested sort.
A Nested Sort is possible when there are two or more dimensions in the view.
With multiple column sorts, it’s useful to understand how sorting in Tableau works. Imagine Tableau as a table; it always sorts by the first column in the table (although the pills to make the tabular columns have to confusingly be on the Rows shelf…). Add another column, then Tableau sorts by the first column, then the second.
A nested sort is when the second column sorts within the the first column. The column sort is restricted to it’s “nest”; i.e. it sorts the second (the sorted) column separately within each first column value. See the image below, sorting by Sales, and note the State sort order is different for each Category.
A non-nested sort happens when the second column is sorted by a measure. The dimension will sort by the measure across the entire view. In the below image, notice how the State sorting is identical for each Category. It doesn’t sort by Sales within each Category. The State sort order is defined by total sales, regardless of the Category, so the sort order is the same within each Category.
How to create a nested sort
There are 2 ways to create a nested sort.
1. Adding a measure to columns reveals a sort icon, showing it’s possible to sort by that measure. Note the sort icon only appears with a value on the Columns shelf. If there’s no value on Columns, the sort icon doesn’t appear.
Using the icon is the simple way to create a nested sort. After using the sort icon, checking the State sort box (right click the pill – Sort) will show it’s “Nested”.
2. Using the Sort box; Nested is an option within the Sort dialog box.
Tableau sort not working!
Now it’s clearer how Tableau sorts, instead of saying Tableau sorting is broken, use that knowledge to “fix” it!
Depending on what you want to sort, there are different options available.
To sort an entire table by a measure, ignoring the nesting, there are two options, both needing a calculated field.
Option 1, create a calculated field, the sum (or other aggregation) of the measure, multiplied by -1, to make it negative.
SUM([Sales]) * -1Add that field to the Rows shelf, make discrete, and put it as the first column. The entire table sorts by that field. In practice, this field should be hidden to the end user. Uncheck “Show Header” on the pill.
See the below example, that sorts across Category and State by highest Sales.
Using a calculated field provides flexibility to the sort. If the number can calculate mathematically, it can sort the table. This approach is used to enable the user to select the sort order.
A downside to this approach; the sorting icons within the table cease to work. They still appear, but do nothing. Hence people think the Tableau sort is broken. It’s not broken, but it’s overruled by the hidden sorting field. But this isn’t clear to any user – to them it looks like the sorting doesn’t work.
Option 2, which keeps the sort icons functioning as a user would expect, is to build a “key field” calculated field, a unique identifier for the table. This key field can “un-nest” the data, and make the sort icons work. The below section describes in detail.
(Note this is a similar technique to that used for showing the top N or bottom N values in Tableau.)
Make sorting icons work in Tableau
In a Tableau table, for the sorting icons to work as the icon suggests they should – i.e. click the icon and it sorts the table by that column – flatten the table.
If all rows in the table are complete, so there’s no grouping or nesting within the table, the sort icons will work as expected.
To un-group/un-nest the table, it needs some form of unique identifier as the first column. Remember, the first column is the primary sorting & nesting column. If the first column is unique, there isn’t nesting, so the Tableau sorting will work.
If the data doesn’t have a unique identifier, it’s simple to construct one using the data in the table. A genuine primary key isn’t needed, as long as it’s a unique field based on the data in the table.
Using the Category & State example, create a unique identifying field in a calculation. Simply combine the Category and State fields in a new calculated field.
To be the sorting field, it has to be the first column in the table. Note, it doesn’t need to be displayed in the table; uncheck “Show Header” to hide.
To demonstrate the sort icons now work, expand the table to have 2 measures. Bring Profit into the table, and convert it to look like a standard table.
Now all 4 columns can sort by their sort icons. Use the sort icon to sort by profit. Clicking the Profit sort icon cycles through the 3 states:
- Sort Descending
- Sort Ascending
- Remove the sort
Measures sort either high to low (descending), or vice versa. Dimensions sort alphabetically using the sort icon, either A-Z or Z-A, cycling through the same 3 states as a measure.
So, this repairs the “broken” sort icons in Tableau, but what are the downsides?
There’s no longer any nesting, so some may find tables harder to read; the white space is gone.
Also, if exporting to Excel, the hidden unique identifier field also exports.
Finally, for large tables with many columns, the calculation to find the lowest level of detail can get long. Plus all numeric and date values in the calculation should be converted to strings, using the STR function, for the calculation not to error.
Example of Tableau sort icons not working, and working
Put the above into practice on a larger table. Below is the Tableau default presentation, with automatic nesting, and nesting any subsequent sort using the sort icons.
Un-nesting (flattening) the Tableau table fixes the sort icons. The hidden “Key Field” un-nests the table, and now the sort icons work on every column. Text columns sort alphabetically, dates chronologically, and numbers ascending / descending.
This is available for download on Tableau Public.
Tableau sorting IS broken. Pretty much like half of most basic functionalities. Lets assume I want to sort based on few fields. Can I do that? The answer is not. I need to cleverly create Calculated Field and then base sort on that… This is definitely laziness of developers as even basic stuff like SQL allows such simple stuff.