Last week I had a light-bulb moment of finally understanding how table calculations were computing using specific dimensions in Tableau. Previously I would just try changing how the table calculation was calculating until I arrived at the right answer without really understanding how it was working.
After watching a video by Andy Kriebel in which he explains how to describe a table calculation in plain English, it finally made sense! I highly recommend checking out his post here for his video explanation and further examples.
What exactly are table calculations?
In Tableau, table calculations are a type of calculation that is computed based only on what is currently in the view. This means they do not consider anything that is filtered out of the visualization. However when using table calculations how they calculate using the dimensions in the view must be specified.
Create a Cross-Tab
When creating table calculations, it can be a good idea to do so in a cross-tab to ensure the calculation is set up correctly and is accurate.
As an example I have set up the following cross-tab with a running sum using Superstore data of sales by Region, Segment and Order Year. As seen below, when editing the table calculation, Tableau will highlight how it is computing in yellow. This helps to visualize how it is calculating and help to find the right computation.
Compute Using Specific Dimensions
As different ways to compute the table calculation are selected, the dimensions in the box below specific dimensions will change in order and in which ones are selected. Computation by specific dimensions can be more flexible, such as in case the dimensions you are computing by is on the marks card, rather then on the rows or columns.
To understand how Tableau computes using specific dimensions, it can be helpful to explain how it is calculating in plain English:
This means for every unchecked dimension, calculate the desired Table Calculation by the checked dimensions. The order of the checked boxes will impact how the calculation is carried out, starting from the bottom to the top.
To make it clearer it can be illustrated in an example. Below the running sum is computed using Segment and Region. Translating this into plain English: for every Year of Order Date, calculate the Running Sum by Region and then Segment. The arrows show the direction the running sum is computing in.
Hopefully this explanation helps you as much as it did for me. If you are now looking for more advanced examples of table calculations, check out The Tableau Book of Calcs created by Sara Hamdoun and Fuad Ahmed. It is full of practical examples, perfect for expanding your Tableau table calculation knowledge.
