Tableau Deep Dive: Table Calculations – Custom Sorts, Part One

Data

Tableau Deep Dive: Table Calculations – Custom Sorts, Part One

Tableau Deep Dives are a loose collection of mini-series designed to give you an in-depth look into various features of Tableau Software.

(Note: All the charts we build are included at the bottom of this article in a packaged workbook available for you to download.)

Table calculations in Tableau can get complicated very quickly. But these extra little features make a big difference, such as computing specific dimensions, secondary calculations and more. My goal with this article and the follow-up is to add some use cases for when you’d use the custom sort option in the table calculations editor. In part two, I’ll explore some specific use cases on using custom sorts. First, let’s review how table calculations are sorted.

Table Calculation Sort Basics

When I enable a table calculation on a measure, Tableau offers two basic options. The first is to use the structure of my viz to determine how and where my table calculation is computed. This is defined by scope and direction. Scope is defined in terms of table, pane and cell. Direction is down, across, undefined or a combination of directions (i.e. down then across or across then down).

When it comes to table calculations, sorting is not the order in which the rows or columns are displayed. That’s just normal sorting that you do on your table. Instead, sorting within a table calculation is the order in which the table calculation is computed. Let’s look at a calculated running sum below as an example:

Table Calculations in Tableau

Here’s how that table calculation would function on a sample text table:

Table Calculations in Tableau

This calculation starts at the top of the pane (or category) and works its way down row by row, adding the next number to a growing sum. This is how Tableau computes this calculation:

Table Calculations in Tableau

Sorting with Specific Dimensions

The second table calculation option is to use specific dimensions within the visualization to determine how the calculation is computed, rather than the chart structure. This has replaced the old addressing and partitioning function from Tableau 10’s pre-version. Let’s continue with the example above where I was using pane (down). Recreate the exact same computation, but use specific dimensions to do it:

Our result is the same, but here’s how it changes what Tableau is doing behind the scenes to compute this calculation:

Table Calculations in Tableau

See the difference? In the first example, Tableau is using the structure of the table to organize the table calculation. In the second, Tableau is looking at the dimensions in the table. This is a crucial difference because it unlocks a lot of cool use cases that we can use with our table calculations. A perfect example of that expanded functionality is custom sorting.

Custom Sorts

Custom sorts appear in the table calculation editor when the Compute Using menu is changed to specific dimensions. Custom sorting is available on all types of calculations except rank and percentile. In example two, I used specific dimensions. Tableau used a default sort order to organize this computation. It started the running sum at the top and continued down each sub-category until we hit a new category.

Because sub-category is sorted by ascending order, the table calculation happened to be in ascending order as well. Custom sorts allow me to completely change that order of computation. Let’s do the exact same computation above, but this time I’ll show you how it would look as a custom sort:

Table Calculations in Tableau

I’m telling Tableau to use sub-category in ascending order. In other words, we are starting alphabetically on sub-category and then adding each subsequent value until we reach a new category. The MIN() function in this example is to satisfy a requirement for matching levels of aggregation.

What happens if I change ascending to descending? Then I would start at the lowest letter of the alphabet within sub-category and work towards the highest. With my sub-category still sorted in ascending order, I’ve effectively changed my scope and direction to pane (up). Take a look at what we changed:

Table Calculations in Tableau

And the change gives you this:

Table Calculations in Tableau

This calculation starts with the sub-category that is lowest in the alphabet and works its way back towards the top (i.e. A). If I decide my sub-category rows should be in descending order, so that tables is at the top of the furniture category, my table calculation still sorts in descending order. What if I change my custom sort to sort on a different field? How about sales? Remember, I need to choose an aggregation, so let’s choose SUM():

Table Calculations in Tableau

Tableau is going to start with the lowest value of sales within each category and then add the next lowest sales as it moves sub-category by sub-category through each category in the table.

Table Calculations in Tableau

Download all the work we have done in this TWBX file

What’s Next?

You can see how this opens a new world of options in how we organize and customize our table calculations. In part two, I’ll apply this feature to two different use cases to do some really neat stuff using the Index() function and another with a Running Sum () across different levels of detail. Please drop any comments you have in the space below. Cheers!

Want More Tableau Deep Dives

  1. Tableau Deep Dive: LOD – Introduction to Detail
  2. Tableau Deep Dive: LOD – The Include Calculation
  3. Tableau Deep Dive: LOD – The Exclude Calculation
  4. Tableau Deep Dive: LOD – The Fixed Calculation
  5. Tableau Deep Dive: LOD – LOD Calculations vs. Table Calculations
  6. Tableau Deep Dive: Parameters – Parameter Overview
  7. Tableau Deep Dive: Parameters – Parameter Properties
  8. Tableau Deep Dive: Parameters – Filtering – Top N
  9. Tableau Deep Dive: Parameters – Calculated Fields
  10. Tableau Deep Dive: Parameters – Filtering Across Data Sources
  11. Tableau Deep Dive: Parameters – Bins
  12. Tableau Deep Dive: Parameters – Reference Lines
  13. Tableau Deep Dive: Parameters – Table Calculations
  14. Tableau Deep Dive: Sets – Introduction to Sets
  15. Tableau Deep Dive: Sets – Constant Sets
  16. Tableau Deep Dive: Sets – Computed Sets
  17. Tableau Deep Dive: Sets – IN/OUT
  18. Tableau Deep Dive: Sets – Combined Sets
  19. Tableau Deep Dive: Sets – Calculated Fields
  20. Tableau Deep Dive: Sets – Hierarchies
  21. Tableau Deep Dive: Dates – Introduction to Dates
  22. Tableau Deep Dive: Dates – Preparing Dates
  23. Tableau Deep Dive: Dates – More Date Functions
  24. Tableau Deep Dive: Dates – Exact Dates
  25. Tableau Deep Dive: Dates – Custom Dates
  26. Tableau Deep Dive: Dates – Rolling Dates
  27. Tableau Deep Dive: Dates – Calendar Filters
  28. Tableau Deep Dive: Dates – Week-by-Week Comparison
  29. Tableau Deep Dive: Dashboard Design – Planning
  30. Tableau Deep Dive: Dashboard Design – Layout & Structure
  31. Tableau Deep Dive: Dashboard Design – Proof of Concept
  32. Tableau Deep Dive: Dashboard Design – Adding Interactivity
  33. Tableau Deep Dive: Dashboard Design – Visual Best Practices
  34. Tableau Deep Dive: Dashboard Design – Optimization & Governance
  35. Tableau Deep Dive: Dashboard Design – Publishing
  36. Tableau Deep Dive: Table Calculations – Custom Sorts, Part One
  37. Tableau Deep Dive: Table Calculations – Custom Sorts, Part Two
  38. Tableau Deep Dive: Table Calculations – Custom Sorts, Part Three

More About the Author

Robert Curtis

Analytics Consultant / ANZ Practice Lead
An Interactive Guide to Public Toilets in Australia Traveling requires planning. Where do you want to go? How will you get there? Where will you stay? What will you eat? These are the ...
Tableau Desktop Now Available in Dothraki With aims to broaden their global customer base, Tableau Software (NYSE: DATA) announced today a Dothraki language version. The ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK