Tableau Sort Table by Measure and dimension

//

Do you want to know how to sort a Tableau table? Sometimes the user may want to sort by a measure, sometimes by a dimension.

Sorting a table in Tableau can get complicated. This post will run through simple sorting, that comes “out of the box”, to more complex sorting using parameters and calculated fields.

Sort a Tableau table by Measure

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.

table of sales by state in Tableau

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.

Sort By A Calculated Field in Tableau

Using a mixture of parameters and calculated fields, it is possible to create custom and dynamic sorts within Tableau.

To sort by a calculated field there are 2 options:

  1. Use the field in the Sort – Field section of the field sort menu
  2. Place the sorting calculated field as a discrete (blue) pill to the left of the field(s) to sort

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.

Making use of parameters and calculated fields, the user can choose 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.

a Tableau string parameter to choose the sort field
Choose the sort field

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.

a Tableau parameter to choose the sort order
Choose the sort order

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.

a tableau calculated field to sort based on parameter selections
The calculated field sorts the view based on the selections in the parameters

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

Sort using the Sort – Field in the pill Sort menu

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.

set the Sort By field to use the sorting calculated field

Sort by putting the calculated field on Rows

The second option is to place the SortStateField on the Rows shelf.

Set it to be Discrete (blue) and drag it to the far left, so is it the first pill on rows. It has to be Discrete; Continuous (green) pills are always on the right, it’s not possible to place green pills to the left of blue.

To make the sorting calculated field not visible simply uncheck “Show Header” on the pill.

Which ever way you implement the sort, the final step is to display the parameters on the worksheet.

Now the sorting by calculated field is complete and the user can choose how to sort the table.

Sorting a Tableau table by a dimension

The question about sorting alphabetically has come up a number of times. Sorting A-Z is actually really simple, it is the default sort 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:

a sorting calculation to sort by parameters or sort alphabetically in Tableau

This will now sort alphabetically, A-Z, whenever Profit, Profit Ratio and Sales are NOT selected.

Sort alphabetically using a formula

Another alternative, with limitations, is to use the ASCII formula. The ASCII formula converts the first letter to a number. That is the limitation, it sorts only by the first letter.

However, there is a workaround.

To sort the Customer State field alphabetically (by the first letter only), use the formula:

MIN(ASCII(UPPER([Customer State])))

To sort this descending, multiply by the SortOrder.

MIN(ASCII(UPPER([Customer State]))) * INT([SortOrder])

To sort alphabetically by more letters, increase the complexity of the calculation.

For example, this formula sorts by the first 3 characters:

ASCII(UPPER([Customer State])) + ASCII(UPPER(MID([Customer State],2,1))) * 0.1 + ASCII(UPPER(MID([Customer State],3,1))) * 0.01

Adding additional letters is simple – alter the number in the MID function to match the position in the string and another 0 to the right of the decimal point.

Note: this is only really useful to sort alphabetically descending. Sorting alphabetically ascending is better using the default alphabetical sort order.

The final formula to sort alphabetically both ascending and descending (with aforementioned caveats) is:

tableau calculated field containing a sort calculation
Note the “-” around the descending alphabetical sort

The // in the formula are comments, that’s how to add comments to a calculated field in Tableau.

In the Tableau workbook, sort Descending by State and check New Jersey, New Mexico and New York. Notice they don’t sort correctly as the formula only sorts Z-A for the first 3 letters.

28 thoughts on “Tableau Sort Table by Measure and dimension”

    • Hi, I think you could be looking for the wrong field. The sorting field is called SortStateField. I’ve updated the post to show the sorting.

      Reply
    • Behind the scenes a date is a number so this technique should (without me actually trying…) work. If not another option is to convert the date to a number for sorting. For example you could build the date based on YearMonthDay – for example 27 Aug 2015 would be 20150827 or 1 Aug 2015 would be 20140801. Converting your dates to numbers like this always sorts correctly but may be completely unnecessary as you might be able to sort by date anyway or just wrapping the date in an INT() function might do it. Let me know how you get on.

      Reply
  1. I am using three metrics from three different data sources ( I have joined all three by using”Edit relationships”) but while sorting on my dimension I do not see the calculated field (equivalent of SortStateField). I suspect it has something to do with the fact that there are different data sources involved. Can you think of a work around?

    Reply
    • It gets tricky once you start using multiple data sources. Can you supply a twbx file as an example and I’ll take a look?

      Reply
  2. @awatson
    hi, I have the same problem, need to sort 2 measure values which come from 2 different data sources. Your solution works well only in the value from primary data source, but not the other. Can you help? Thank you.

    Reply
    • This is a bit more tricky but still possible. As per the post still create the sorting field, but don’t use it in the Sort area of the field – this will error as you probably noticed. Instead drag the sort by field into the rows, set it as a discrete value, make sure it’s the FIRST value on your rows shelf, as Tableau sorts by the first value by default.

      The default sort if ascending, which could mean you need to get a bit smart with multiplying by -1 and 1 to get the correct sort order. You can do this by tweaking the SortBy calculated a little.

      I also suggest hiding the field by unchecking Show Header once it’s working.

      Hope that helps.

      Reply
  3. Can you please suggest a work around if all the fields are text fields?? and one field is a date field. can we still achieve this functionality?

    Reply
    • A date field could be converted to a number for the purposes of sorting – i.e. 1 May 2016 could be converted to 20160501. That format will always sort correctly.

      Reply
  4. Can anybody help me out how to sort all Dimensions(Atleast 1) and 1 or More Measure(s) in a view in Tableau ?

    Reply
  5. This is awesome. But I have a question here, how to we do sort order for multiple dimension columns A-Z to Z-A and vice versa?

    Reply
  6. Hello everyone

    my requirement i want to sort by category,region,segment with asending or desending order .can you please help me here.
    it is working sorting with country,state,city used calculation is CASE [dimension]
    WHEN “catagory” THEN [Category]
    WHEN “segmnet” THEN [Customer Segment]
    WHEN “region” THEN [Region]
    END
    but not wrking with asending or descending order..plg help me with calculation.

    Reply
    • Hi, it’s not clear, did you manage to resolve this or still would like some assistance?

      Reply
  7. @awatson
    I’ve set this up for my data but I’m having trouble getting it to work with my Set and I believe the problem is something to do with me using Year as a filter.
    In the Top tab under Edit Set, if I sort by Field and select SortStateField then my data in the Set sorts by ALL years rather than the year selected in the Filter. I have a feeling I may be able to rectify this using the By Formula button in the Edit Set – do you have any idea if that would be possible?

    Reply
  8. Hi there, this is really helpful. However i have some difficulties here. I have 2 calculated measures that using str() to convert value to string and have it in parameter so user can either view it by number or percentage as i want to display the value with % when percentage in parameter is selected. But once i have done this the sort icon by default on colomn header in no longer there. Is there anyway around this?

    Reply
    • In that case I think you have little choice but to sort using a parameter instead of the sort icons. Or you show the 2 measures (% and actual) at the same time, keeping them as numbers so the icon appears.

      Reply

Leave a Reply to Ankamma Chowdary Cancel reply