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.
Reference: A Handy Guide to Choosing the Right Calculation for Your Question
Author: Neil Li
Neil graduated from the University of Melbourne with a Master's degree in Information Technology. He started his career as Data Analyst in a Tech Company in China. Recently, Neil moved back to Australia and decided to join The Data School, kick-starting his career with Data Analytics in Down Under. He looks forward to learning cutting-edge skills and meeting fellow data enthusiasts who share a similar passion for data. In his man cave, you will spot Neil enjoying his video games and reading. He steps out with his racquets to swing it for Tennis and Badminton. Want to put a smile on his face? Ask him about the Chinese Hot Pot!