8 min read

 

This blog aims to illustrate how to decide on the right type of Tableau calculation to use. First, we will explore the characteristics of the three types of Tableau calculations, namely basic calculations, LODs and table calculations through examples based on the Sample – Superstores dataset. Secondly, we will propose a series of questions that can guide us through the process of picking the appropriate calculation. Finally, we will summarize the complete strategy for determining the right type of calculation for your problem with a decision tree. 

This blog has been inspired by this Tableau blog. If you are interested in this topic or would like to see some additional examples, I would encourage you to check it out!

 

Contents

  1. The Types of Calculations in Tableau
  2. Basic Calc vs. Table Calc vs. LODs
  3. Basic Calc vs. LODs
  4. Table Calc Only
  5. Summary

 

Tableau is powerful. It offers a suite of functionalities and multiple forms of calculation that allow the designer to create virtually any Viz. But this flexibility also makes calculations one of the most challenging aspects of Tableau. It can be tough to determine which calculation to use in a given situation, even for people with years of experience using Tableau.

 

 

1. The Types of Calculations in Tableau

1.1 Basic Calculations/Expressions

Basic calculations or expressions allow you to perform calculations at either the data source granularity (a row-level calculation) or at the granularity of the Viz (an aggregate calculation). 

It is important to note that basic expressions are queries sent to the server and therefore are performed at the data source level. Inefficiently written basic expressions can slow down your dashboard performance!

 

1.1.1 Row-level Calculation

Row-level calculations literally mean calculations that occur at each row in the table, which is the most detailed level of granularity in a data source.

Let’s see an example. Assuming that our Sales field was recorded without GST. If we want the correct sales value that is inclusive of GST, then we can perform a row-level calculation by multiplying each Sales row by 110% (or 1.1).

 

1.1.2 Aggregate Calculation

Tableau aggregates the data before displaying it on the canvas. By default, aggregation occurs at the most granular level in a Viz, this can be different from the most granular level in the data source.

The most granular level in the data source is always each row, it is independent of the Viz itself. Whereas the granularity of the Viz is defined by the dimensions we are using to visualize the data.

For example, if we want to know the unique number of customers in the dataset, we can use COUNTD([Customer Name]), and we see that there are 793 unique customers. In this case, the count is occurring at each row (counting the number of rows), and since we have no dimension in the view, the Viz level aggregation’s granularity happens to be the same as the data source granularity.

 

But if we now include the Segment dimension in the view, we see that the COUNTD calculation is now aggregated at the Segment level of detail. The granularity of the Viz changes with the dimension we have in the view!

 

 

1.2 LODs (Level of Detail expressions)

Like basic expressions, LODs allow you to perform calculations at either the data source level or the Viz level. The difference is that LODs give you much more control over the level of granularity. LODs allow us to override default aggregation and can be performed at a more granular level (INCLUDE), a less granular level (EXCLUDE), or an entirely independent level (FIXED) than what is in the view.

It is important to note that LODs are also queries sent to the server and therefore are performed at the data source level. Inefficiently written LODs can slowdown your dashboard performance!

Let’s again use the number of unique customers as an example. We see that if we use an EXCLUDE LOD that excludes Segment from the calculation, then we get back 793 customers, which is less detailed than the dimension in the view. 

 

 

1.3 Table Calculations

Unlike basic and LOD expressions, table calculations are computed locally after the data has been aggregated. This makes table calculations potentially much faster as they don’t need to query the server. However, table calculations can only operate over values that is in the view.

For example, in the screenshot below, we are performing a running total across each of the sub-categories. We can do this because we have the Sub-Category dimension, and therefore the corresponding data in the view.

 

But if we remove the Sub-Category dimension, then we see that the same running total calculation will only return the cumulative sales at the Segment level, this is because Segment is the only data in the current view.

 

 

2. Basic Calc vs. Table Calc v.s LODs

Now that we have established a good understanding of the characteristics of the different types of calculations in Tableau, we can begin asking a series of yes or no questions to help us determine which is the most suitable type of calculation to use.

Firstly, we want to ask:

Do I already have all the data values I need on the Viz?

If yes, then often table calculation will be the best choice, as it tends to be faster than both basic expressions and LODs.

If no, then we will have to use a basic expression or an LOD, which we will discuss in more detail in the next section.

Sometimes, we may have all the data we need, but it is not possible to achieve the required layout using a table calculation. And so we need to ask:

Is the layout of the Viz compatible with a table calculation?

If yes, then we can simply use table calculations.

If no, then we will have to use a basic expression or an LOD.

 

 

3. Basic Calc vs. LODs

In order to decide between basic calculation and LODS, we need to ask the question:

Does the granularity of the question match that of the Viz or that of the data source?

If yes, then we can use a basic calculation. 

If no, we will need to use an LOD expression.

For example, assuming we want to know the average customer’s profit for each sub-category. Simply setting the aggregation of Profit from SUM to AVG will not give us what we want, since it is just calculating the average profit per sub-category (profit divided by number of orders for each sub-category) without taking into account of individual customers.

Since the granularity of our question is based on customer name, which is different from the granularity of the current Viz, we need to use LODs to help us answer the question.

Here, we can use the INCLUDE LOD to find the profit per customer, and then average across customers to find the average customer’s profit per sub-category (sum of profits of all customers in a sub-category divided by the number of customers in that sub-category).

 

 

4. Table Calcs Only

Interestingly, there are several classes of problems that can only be solved using table calculations, and they are:

  • Ranking
  • Recursion (e.g. cumulative values, such as running total)
  • Moving calculations (e.g. moving averages)
  • Inter-row calculations (e.g. period to period comparisons)

Therefore, we should also ask:

Does the problem require ranking, recursion, moving calculations or inter-row calculations?

If yes, then we will use table calculations.

 

 

5. Summary

Finally, let’s summarize our complete strategy into a convenient decision tree!

 

 

 

 

 

Martin Ding
Author: Martin Ding