Tableau’s sorting function can be tricky. Have you tried sorting a grand total column that aggregates values from calculated fields, or maybe tried using a window (across/down) calculation instead of the grand total? In other words, have you tried sorting a dimension by a table calculation of calculated fields?
Unfortunately, directly sorting on aggregates of calculated fields isn’t possible in Tableau. Instead, you’ll need a level of detail workaround. I’ll show you this workaround using an example from the Tableau Bookstore database below.
Don’t forget, for more data fundamentals we provide free eBook with loads of tricks to help you be more productive and drive results.
Rule of Thumb for Grand Total Sorting
The general rule for sorting grand total columns in Tableau is that the grand total must be the automatic aggregation method. If you change the aggregation method to “SUM” via Analysis > Totals > Show Row Grand Totals, for example, the functionality disappears.
In many cases, the default aggregation is the one you want, so you won’t have any trouble. However, if you need to change it, you’ll need a workaround.
Make Sure You Know Tableau Tricks like Grand Total Sorting
Tableau is a powerful tool, but it’s only as good as your knowledge of it. Consider writing this down somewhere and keep a running list of Tableau “tricks” like it. I also encourage you to cover the essentials with our Tableau Quickstarter, or take the free [qsm_link id=1]Tableau knowledge quiz[/qsm_link] to see what you know!
Workaround for Sorting on Aggregates of Calculated Fields
Rather than using FIXED on the desired aggregation dimension or measure, the workaround is to create a FIXED level of detail calculation that aggregates the smallest level of detail dimension in the view by fixing it on highest level of detail of the same dimension and the desired aggregation dimension. This is difficult to envision with words alone, so let’s look at an example.
Example Using Tableau’s Bookstore Database
Imagine you have a list of book ISBNs and the number of times they are purchased in a given period. It looks like the following picture:
Download Tableau’s Bookstore Database
Download the Tableau Workbook for This Article
The list of books is much longer than what we see in this picture, but this should give you an idea. The values shown are a calculated field that counts the number of times the Isbn was purchased in each month.
As you can see, some cells are blank because there was no sale of Isbn in that month, but this won’t interfere with out analysis.
Imagine we want to take the total of sales made across these months for each Isbn and sort on it in order to see which were purchased most often. There are two intuitive ways to do so. Either (1) we use Tableau’s total column functionality, or (2) we create a WINDOW() calculation to get the same result.
Total Column Functionality
Using the total column functionality, we navigate to the functionality at Analysis > Totals > Show Row Grand Totals. The result looks like this:
However, if you place your cursor over the Grand Total header or hover over the sort buttons, you will see that sorting on the grand total is not possible. The sort button will default to a random sort, and the Grand Total column header has no sort option.
WINDOW() Calculation
Alternatively, we can try to build a WINDOW() calculation that will create a new column for each month but count only the # of Books. The calculated filed looks like this:
When we pull this calculation into the field, the result looks like this:
However, if we hover our cursor over the Grand Total Window Calc month headers, the sort option is unavailable. In addition, if we use the sort button, Tableau may or may not use a random sort. In some cases, it works, but not in all. The sort button on a WINDOW() calculation is not a solution.
Workaround
In order to guarantee that the sorting option works, we can create the following FIXED level of detail calculation.
This calculation works by fixing the desired level of detail in our view, which is month of sales date, on the total period present in the view, which is year of sales date, and the dimension we want to count, which is ISBN (Edition).
This structure may seem strange since the dimension we want to count — ISBN (Edition) — is not the measure of the FIXED formula. Essentially, we’re flipping the logic of the calculation on its head so that the sorting dimension is a count of months in which a book was purchased at least once, rather than counting the number of counts of the book. The latter is a double aggregate, which is impossible. Our view will now look like the following:
As you can see, we can now filter directly on any of the three column headers. It does not matter which one, since they all represent the same aggregate calculation of the # of Books.
Conclusion
If you found this article helpful, check out more free content on data, finance, and business analysis at the AnalystAnswers.com homepage!
