×

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:

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

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:

## 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:

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:

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:

And the change gives you this:

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():

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.

## 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!

## Robert Curtis

Managing Director, APAC
Building Solutions with InterWorks at Corinium’s Data Architecture Conference in Melbourne InterWorks was a proud sponsor of the Data Architecture Conference hosted by Corinium in Melbourne on 21 and 22 June 2023. Hundreds of ...
Virtual Event Recap: Deep Dive of Tableau Parameters For our June Lunch and Learn, we did a deep dive on parameters for our friends across Australia and Singapore. Tableau is the premier ...

See more from this author →

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

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×