There are 3 main types of calculations you can use to create calculated fields in Tableau:
- Basic expressions: these allow you to transform values or members at the data source level of detail (a row-level calculation) or at the visualisation level of detail (an aggregate calculation)
- Level of Detail (LOD) expressions: similar to basic expressions, LOD expressions allow you to compute values at both the data source level and the visualisation level. However, they give you more control on the level of granularity you want to achieve.
- Table calculations: allow you to transform values at the level of detail of the visualisation only.
Choosing the right type of calculation to use for your analysis is not always easy. This blog aims to give you some guidance on how to decide which calculation to use.
Basic expression or table calculation?
Question 1: Do you already have all the data values you need on the visualisation?
Basic expression or LOD expression?
If you don't have all the data you need on the visualisation, you need your calculation to be passed through to the data source. This means you need to use a basic calculation or a LOD expression.
If the answer to question 1 was no ask yourself this:
Question 2: Does the granularity of your question match either the granularity of the visualisation or the granularity of the data source?
Table calculation or LOD expression?
When choosing between a table calculation and a LOD expression, the process is very similar to choosing between a table calculation and a basic expression. Ask yourself the following questions:
Do you already have all the data values you need on the visualisation?
When you should use table calculations only
There are some instances when you should only use a table calculation - these include:
- Ranking
- Recursion (e.g. cumulative totals)
- Moving calculations (e.g. rolling averages)
- Inter-row calculations (period vs. period calculations)