As we’ve delved further into the intricacies that Tableau has to offer, a number of useful tools and functions have presented themselves as crucial bits of information in a journey towards being competent Data Analytics Consultants. One such group of functions are Tableau’s Table Calculations, which essentially allow you to perform calculations and computations on values that are in view and in tables. This gives you an additional way to manipulate and visual data, alongside Tableau’s Level of Detail functions (‘LODs’), which operate over an entire data source. The picture below shows Tableau’s order of operations, and how ‘table calcs’ are applied after everything else has already run, allowing for a more granular level of detail.

While a number of features can be accomplished using LODs, table calcs still have their time and place. Let’s take a look at a few of the most important and useful ones to remember.

Quick Table Calculations

The first table calcs we’ll look at are the quick table calculations found when you right click on a continuous measure. There you’ll find table calcs such as ‘Running Total’, ‘Difference From’, ‘Percent Total’, and ‘Moving Averages’.

If you click into ‘Add Table Calculation’, or if already added, ‘Edit Table Calculation’, you will find a menu that looks like below – here you can compute the data in different ways, changing the scope and direction. If you choose specific dimensions, an important thing to remember is if you untick a box, you are making the calculation type restart counting when it comes across a new value, while keeping it ticked would mean you are continuing along the dimension as the value changes.

FIRST, LAST, INDEX, and SIZE Calculations

You can also create table calcs in calculated fields, which is where you would create these calculations.

First and Last are fairly self-explanatory – they return the number of rows (offset) from the first or last cell in your grouped data (partition).

Index returns the index value of a cell in a partition, beginning at 1. This usually means the first row index starts at 1.

LOOKUP and RANK Calculations

While you can find Rank calculations in quick table calculations, they are also created through calculated fields, as are Lookups.

Rank calculations returns a value’s respective competition style and ranking for the row in a partition. Variations of Rank include RANK_DENSE (missing rank for a duplicate value is not skipped), RANK_MODIFIED (duplicate values get the same, but lower, rank), RANK_PERCENTILE (returns a percentile rank for the current row) and RANK_UNIQUE (ranks duplicate values based on sort order).

RUNNING Calculations

There are a number of Running calcs which are useful, but the most common you would see is the Running Total, located in quick table calculations. While they cumulatively aggregate marks across a defined window, the different variations provide a certain, fairly self-explanatory aggregation – these include SUM, AVG, COUNT, MIN, and MAX.

WINDOWS Calculations

Our final calculation to talk about, Windows, aggregates data to a higher level than what’s in your view and takes an expression by the offset from the current row. There are a large number of different variations which affect how the expression is manipulated, including WINDOW_SUM, WINDOW_AVG, WINDOW_MIN and WINDOW_MAX.

Nicholas Seah
Author: Nicholas Seah