Calculations in Tableau
What exactly is a calculation? Although it appears straightforward, Tableau distinguishes three types of calculations. Calculations are written either as part of the data source query or after the query. This will make more sense in a moment. There are three types of calculations: basic calculations, level of detail expressions (LOD), and table calculations.
Basic calculations can be aggregate calculations, such as a sum or average, that work on the entire dataset, or they might work on a single row of data. The query includes some basic computations. Tableau queries include LOD Expressions as well. They allow us to “calculate aggregations that are not at the level of detail of the view,” according to Tableau. The EXCLUDE, INCLUDE, and FIXED functions allow LOD expressions to work at a more detailed level. Table calculations are written after the query, which means they operate on the values calculated as the query’s results. Although we are capable of writing long and complex calculations, sometimes simple is enough.
Table Calculation in Tableau
Table calculations are a subset of calculated fields in Tableau Desktop that perform modifications (i.e. more math) on values in a graphic. Table calculations commonly include running sum, moving average, and percentage of total. Within Tableau, calculations are performed on local data (post-filtered data). The following are key concepts to remember:
- The arithmetic for table computation will be dependent solely on dimensions (granularity) within the view.
- Local data indicates that calculations take place after all filters have been applied.
This means that how a visualization is built, which dimensions are added, and what data is filtered out all play a key part in ensuring that table computations perform as intended.
Four Main Calculation in Tableau
Here are the four main calculations that can be done in Tableau and these are as follows:
- Row Level Calculation
- Aggregation Calculation
- Table Calculation
- Level Of Detail Expressions (LOD)
Let’s look at each one of them in detail.
Row Level Calculation
Row-level calculations are performed on each row of the data collection. Consider the following data set:
Now, to compute the revenue, add the following calculation:
We’re requesting that Tableau include a column—a field—that will be calculated using other fields from the same row. In Excel, it would be equivalent to adding a column for a computation that is computed horizontally. Below in the table you can see the new calculated column marked as yellow:
Tableau must aggregate the data in order to visualize it at the appropriate level of detail. The Level of Detail (VizLOD) of your visualization is the level represented by the marks on your Tableau canvas, which is dictated by the dimensions you are using to depict your data. So, if your marks are bars representing months, the month is the level of detail at which Tableau must aggregate the data. Using our previous example, we can add the following formula to see Revenue at the month level:
This means that Tableau will draw each bar using four rows from our data source. In Excel, it would be similar to summing a column—doing a vertical computation.
Table calculations are performed after the aggregate. You can use a Table Calculation to aggregate further, similar to a running total, or you can compare the aggregated values by computing a rank. Assume we want the Revenue running total. Our formula will be:
Tableau will do this computation in three steps:
- Compute the record level: [Revenue]
- The aggregation calculation: Sum ([Revenue])
- Calculation for the table: Running SUM(SUM([Revenue]))
This is illustrated below:
Level of Detail Expression
LOD expressions are used to execute complex queries involving multiple dimensions at the data source level rather than bringing all of the data to the Tableau interface.
Types of LOD
LOD expressions are classified into three categories.
FIXED LOD: This expression computes values using only the dimensions supplied, with no reference to any other dimensions in the view.
INCLUDE LOD: This level of detail expression computes values based on the supplied dimensions as well as any dimensions in the view.
EXCLUDE LOD: These formulas for levels of detail remove dimensions from the view level of detail.