Table calculations are a super useful part of Tableau, but they can often feel unpredictable.
This is because table calculations depend on the structure of the view. Unlike a normal calculation, which works with the underlying data, a table calculation works across the marks Tableau has already created.
So before asking what calculation you need, ask: What does one mark represent in this view?
Start with the Marks
Dimensions control the level of detail and therefore the number of marks in the view:
- With Month in the view, one mark might represent one month.
- Add Segment, and each mark might now represent one segment within one month.
- Add Category as well, and each mark might represent one category, within one segment, within one month.
This matters because the table calculation now has more marks to work across. Adding a dimension to Rows, Columns, Colour, Detail or another part of the Marks card can therefore change the result.
This is why adding a dimension to the view after setting up your table calculation changes the way it works - where possible, it is best to have all the dimensions you need in your view before adding any table calculations.
Specific Dimensions vs Table Down or Across
Tableau offers options such as Table Across, Table Down, Pane Across and Pane Down. These can be useful, but they depend on how the view is arranged. Moving a field between Rows and Columns can change how the calculation behaves.
To keep the table calculation consistent regardless of where a dimension sits, and to have the most control over what is being calculated, it is better to use Specific Dimensions. Instead of telling Tableau to calculate across or down, you specify exactly which dimensions it should calculate across. This means the calculation will not change simply because you move a dimension from Columns to Rows.
The most useful rule to remember is:
For each unticked dimension, calculate across the ticked dimensions.
Ticked dimensions are the dimensions Tableau moves across. These are called addressing dimensions.
Unticked dimensions divide the calculation into separate groups. These are called partitioning dimensions, and the calculation restarts for each combination of them.
Example: Running Total
Imagine you want a running total of Sales by Month for each Region, and your view contains Region, Month and SUM(Sales). The calculation needs to move across Month and restart for each Region.
In Specific Dimensions:
- Month is ticked.
- Region is unticked.
In plain English:
For each Region, calculate the running total across Month.
If Region were also ticked, Tableau would calculate across both Region and Month instead of restarting separately for each region.
Example: Percent of Total
Now imagine you want each Segment’s share of Sales within each Region. The calculation needs to move across Segment, while each Region should have its own total of 100%.
In Specific Dimensions:
- Segment is ticked.
- Region is unticked.
In plain English:
For each Region, calculate the percent of total across Segment.
This is often a more useful way to think about table calculations than simply asking which direction Tableau should calculate in. You are deciding which marks belong together and when the calculation should restart.
Debugging Table Calculations
When a table calculation does not behave as expected, check:
- What does one mark represent?
- Which dimensions are in the view?
- Which dimensions should the calculation move across?
- Which dimensions should it restart for?
- Which dimensions are ticked and unticked?
- Has anything on Colour, Detail, Label or Tooltip changed the level of detail?
- Is the sort order correct?
Sort order is particularly important for ranks, running totals, moving averages and differences from previous values because these calculations depend on the order in which the marks are evaluated.
Hopefully, these examples make Tableau table calculations feel a little less mysterious!