Advanced Table Calculations

Table calculations rely on two types of fields: addressing and partitioning fields. The key to understanding table calcs is to know how these fields work.

  • Partitioning fields define the scope: They break up the view into multiple partitions or sub-views. The table calculation is then applied to the marks within each partition.

  • Addressing fields define the direction: They define the “direction” that the calculation moves (for example, in calculating a running sum, or computing the difference between values).

How we define these in Tableau, depends on our desired amount of control over the end result.

  • Quick Table Calculation: Contains all dimensions in the level of detail, either for partitioning (scoping) or for addressing (direction). Tableau identifies some dimensions as addressing and others as partitioning automatically, as a result of your selections. This can be altered with Compute Using, however it is subject to the structure of the view.

  • Add a Table Calculation: Table Calculations can also be added using Add a Table Calculation from a Measure’s context menu. This allows you to determine which dimensions are for addressing and which are for partitioning using Specific Dimensions.

  • Compute Using: Selecting “Compute Using” from the Measure context menu allows us to compute the Table Calculation based on the architecture of the table, or on a particular field. Bear in mind that a change in the structure of the view will also change your results.

  • Edit Table Calculation: Select “Edit Table Calculation” from the Measure context menu to specifically define the fields to partition and address in the view. Partitioning and Addressing defined with “Specific Dimensions” will hold your results regardless of architectural changes in the view.

Percent Change From a Reference Date

With table calculations, you can calculate the percent change from an arbitrary value. In this example. we will calculate the percentage of changes of COVID-19 cases with respect to a reference date that is chosen as June 1st, 2020. In order to calculate the percentage change, first we need to define our reference date as a parameter called Enter Reference Date:

Furthermore, create a new calculated field which will return the summation of cases on closest point with respect to the reference date, and null for all other days in our visualization. In this example this is called Sum Cases Close on Reference Date.

Finally we need to fix this single returned value to 100%, and all other values to be the percentage change of those. Create a new calculated field called % Difference with the following calculation: 1+((SUM([Cases]) - [Sum Cases Close on Reference Date]) / [Sum Cases Close on Reference Date]). Put this percentage change calculation on the Row shelf.

Finally, the visualization of percentage change of COVID-19 cases with respect to a reference date (i.e. June 1st, 2020) is shown here.

Difference From Calculation

In order to calculate the difference of data from a certain point, for example previous one, you can use Difference From calculation. In fact, in this example, we want to calculate the daily changes of COVID-19 cases. As a result define a new calculated field as Daily Cases:

SUM([Cases]) - LOOKUP(ZN(SUM([Cases])), -1)

Now, let's visualize the DAY(Date) versus Daily Cases.

In order to reproduce the chart visualized above, you need to combine bar and line charts by clicking on Dual Axis.

Using Built-in Table Calculations

Instead of writing formulas, there is an easier way to calculate difference from or percentage difference from by adding Table Calculation.

In fact, there are several built-in functions for calculating various interesting parameters such as:

  • Difference From

  • Percent Difference From

  • Percent of Total

  • Running Total

  • Moving Calculation

For example, it is possible to control the point that you want to calculate difference or the mode that you want to calculate the moving calculation, etc.:

In this example, we choose the Calculation Type as Moving Calculation and summarize the data with averages in a 10 days window.

The workbook to reproduce these visualizations is shared here: https://bit.ly/35lpZuH