Sometimes, choosing the right type of calculation for a given problem can be challenging in Tableau. In fact, there is a simple and quick way to make the proper decision. I’ll start with a brief introduction of three types of calculations in Tableau.
Basic Calculation: written as part of the query, with granularity only at the data source level or the visualisation level.
LOD: written as part of the query, can have any granularity.
Table Calculation: performed after the query runs (usually faster), able to do ranking, recursion, moving calculations, or inter-row calculations.
Obviously, each calculation type has its limitations. So how can we decide to choose the proper one for a problem? Well, the picture below can help you sort it out.
To help you understand the process, I made three gif showcases. The data source is the Tableau Sample – Superstore Data.
Case 1: What are the top 5 total sales states?
For Top N cases, we need to do ranking first, so the easy way to solve this problem is to use a table calc RANK().
Case 2: Show the total sales for each quarter of 2018 and 2019 and the difference between them, and Display the results in a table with three rows: 2018 sales, 2019 sales and difference.
In this case, although we have all the data value needed in the visualisation, we cannot achieve the required layout.
So, instead of using table calc, we can use basic calculations to create new measures and drag the measure names to filters to get what we need.
Case 3: Calculate the percentage of overall sales (including all years) for each quarter of 2018 and 2019.
In this case, when we try to use table calc to calculate the percentage of total, the calculation only based on the data of the year 2018 and 2019. We need the granularity of the data source level to calculate the overall sales. So we need to use a LOD.