Often users of your Tableau dashboards will want to know how to sort a table by different measures. If your table is a simple measure names / measure values this is very simple. Hold the mouse over the column name in the title and a small sorting icon appears.
However often tables are more complex. Perhaps the table was constructed using the placeholder technique, enabling a mixture of dimensions and measures to be displayed in the same table, along with more advanced conditional formatting. These may mean the simple columnar sorting doesn’t work.
Whatever the reason, using a mixture of parameters and calculated fields, it is possible to create custom and dynamic sorts within Tableau.
For this example I’m going to use the Superstore Sales sample data that Tableau provide. I will create a table showing the Customer State on the rows and Profit, Profit Ratio and Sales on columns. For this example I use the Measure Names / Measure Values technique to build the table. If unsure how to do this check my other post on displaying data in tableau as a table.
I understand with a simple table, such as this example, sorting is simple using the Sort icon that appears when hovering over a column header. However the purpose of the post is to show a way to sort using a calculation.
I am going to make use of parameters and calculated fields to enable the user whether they want to sort this table by Profit, Profit Ratio or Sales. They will be able to sort Ascending or Descending.
Create the parameters and calculated field for sorting
The first step is to create a parameter called SortBy. This contains the names of these fields for the user to select.
Next step is to create another parameter, SortOrder, which allows the user to choose whether to sort Ascending or Descending. You’ll notice the value returned by the parameter is either 1 or -1. This is needed within the sorting calculation.
Now we have created the parameters the next step is to create a calculated field using those parameter values.
This calculated field is the sorting calculation.
The SortOrder parameter returns either a 1 or -1. However as the parameter type is a String the values returned are strings. Therefore use the INT function to convert the 1 or -1 to a number. Once it’s a number use it to sort ascending or descending simply by multiplying. Multiplying by -1 will convert the largest number to the smallest and vice versa.
Note the aggregation in the formula of both the Profit and Sales. Tableau does not allow combining of aggregate and non-aggregate functions. Profit Ratio is pre-aggregated, therefore there is no choice but to aggregate all of the fields in the formula.
To implement the sort there are two options
One is to use the calculated field to sort the [State] pill. Right click on the pill on the Rows shelf and click Sort. In the Sort options there is a choice to Sort by Field. Sort by the SortStateField calculated field. As the aggregations are already predefined in the calculation “Aggregation” will default to Custom .
The second option is to place the SortStateField on the Rows shelf. Set it to be Discrete and drag it to the far left, so is it the first pill on rows. Make sure it’s not visible – uncheck “Show Header” on the pill.
How it is when all in a dashboard
Finally display the parameters on the worksheet. Finally it’s complete and the user can choose how to sort the table.
Edit: Sorting alphabetically
The question about sorting alphabetically has come up a number of times. Sorting A-Z is actually really simple, it is the default behaviour. Add another value into the parameter, call it “Alphabetic”. The actual text doesn’t matter, the important thing is only one additional value is added to the parameter.
A minor edit of the sorting calculated field will now enable to alphabetic sorting. Add an Else component to the IF statement; ELSE 1 will do the job. The SortStateField will become:
Sorting Z-A is more complicated. There is a table calculation solution at this link: https://community.tableau.com/thread/249407