One problem I’ve run into several times has to do with how Tableau sorts data. When you use the built in sorting buttons on a visualizations, it doesn’t always sort in the way you may hope or expect. Generally the “problem” comes in when you have multiple dimensions. For example – when you have a simple Sales/Profit by State – clicking the sorting button on Sales seems to do what you would expect (In other words, sorting everything asc or desc by Sales)
Now what happens when we add City and try again?
Well – it turns out Tableau now uses “Nested Sorting”. In reality – it’s correct, but what happens if I want to sort everything by Sales?
There are a few ways to accomplish this but they each have downsides. What to use sort of depends on how much data you have and desired usability. Commonly I choose to use parameter based sorting. The big downside of this is that you must display a parameter with your viz, and it’s a bit more “complex”. But – it does allow each column to stay separate and things “look” normal otherwise.
The first step is to create a parameter which list all the options you want to users to be able to sort.
The second step is to make use of the rank() function in Tableau which functions very much like the rank within SQL (yet easier to use). You’ll want to rank by all dimensions and measures in your parameter.
Note a few things I’m doing here. First – I’m ranking ‘asc’ in each function. Really this is purely up to the developer – but switching these in each will help you determine your default positions. Secondly – my attributes are surrounded by the attr() function. Since I can’t mix aggregates with non-aggregates, commonly attr() is used with attributes to get around this very common error (depending on the situation). Lastly, I use rank() and not rank_unique(). In this context, it really doesn’t matter – but there could be situations in which you want rank_unique() so it doesn’t skip numbering (especially if you’re showing the rank number to users).
Next I put the rank on the rows and since it’s considered a table calculation, I change to sort by Table Down.
Finally – we’ll hide the column for Rank Sort so it’s invisible to users.
Testing out using State and Sales, we’ll see the drastic difference in sorting.
All done. Now – there is another way to go about this and it requires combined fields. On small visualizations this tends to work very well, but combines State/City into a single column, which may not be what you particularly want either through usability or visually. However – imagine doing this on 3 or 4+ dimensions, not my favorite.
The way we can solve this problem is by keeping the State and City dimensions separate, to the right of the combined field on the rows.
We can now hide the combined field and use the Profit/Sales sorting as normal. However – this only works for the measures and not the dimensions (so there is a trade off here).
For a copy of the workbook I used please download here. Uses the Superstore example data source.