Introduction to Table Calculations

1. Introduction to Table Calculations

Welcome back. In this chapter, we will first focus on Table Calculations and then we will zoom on parameters in calculations.

2. What are Table Calculations?

Let’s start with the Table Calculations. This is a special type of calculation that transforms the values in a visualization based only on what’s currently on the canvas and disregards all other measures and dimensions. Table Calculations are computed on local data in Tableau. Tableau does this by storing the necessary data in a virtual table, which may vary greatly from how the underlying dataset looks like.

3. Table Calculations vs. data source

Let’s consider the following example. In our underlying dataset, each order is logged separately. However, in the visualization, we perform an aggregation of the profits and present them by product. Note that the Sum of Profit is not available in the underlying source as it’s only calculated in the view. Tableau stores all necessary ingredients of this visualization in a virtual table which can then be used in Table Calculations.

4. Examples of Table Calculations

Let’s consider some of the most common examples of Table Calculations. One of them is a running or in other words, cumulative aggregation, for example, the sum of profit. Another one is calculating % of the total.

5. Examples of Table Calculations

Yet another example is comparing preceding and subsequent data points, for example, the difference in the sum of profit from the current quarter. Another very common case is ranking, for example, ranking quarters based on their profit performance.

6. Quick Table Calculations in Tableau

7. Multiple dimensions: addressing and partitioning

However, these calculations can quickly get tricky when working with multiple dimensions. Let’s consider a table of yearly profits per product per month as presented on the left side. If we wanted to calculate the running sum of profits, there are several possibilities. In the first example, marked in red color, we calculate it per year and month, so for all the products. In the second example, marked in pink, we could do it per year and per product separately. We need to give Tableau clear instructions on how we want to compute it. This brings us to an important concept of addressing and partitioning. One or more dimensions defining how to group the calculation are called partitions. They determine the scope of the calculation. In the first example, we partition by year and month, while in the second example, we partition by product and year. The remaining dimensions in the view are called addressing fields, and they determine in which way the calculation moves. For example, ordering months from newest to oldest would impact our running sum calculation. The good news is that in Tableau, you can fully control both partitions and addressing fields in your calculations.

8. Formulas behind Table Calculations

Let’s have a look under the hood and zoom in on a few of the underlying Tableau formulas. The running functions, such as running sum, average, and other aggregations, always compute based on the dimensions in the view from the first row in the partition to the current row. We can adapt the direction and order, for example, Table down, pane across, etc. They define where the calculation needs to restart. Window functions perform the aggregation within a window defined as offsets from the current row and are very useful in moving calculations. We will come back to them in the final chapter.

9. Tableau's Table Calculations assistant

Basic Table Calculations take quite some time to master… and you still haven’t seen the nested Table Calculations! Luckily Tableau has a built-in calculation assistant, showing us what changes in the calculation when we adapt the computation type.

10. The dataset: UK clothing business

Finally, our new dataset: in this chapter, we will deal with sales database of a large UK clothing business, operating across the island through a network of retailers and chain stores.

11. Let's practice!

Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.