1. Table Calculations in action
We will see Table calculations in action using the retail order and sales database.
In Tableau, we have Orders and Products data, so let’s investigate how the volume of Orders evolves across time and Product categories.
Let’s make a line chart and expand it to a monthly view.
With many spikes, it’s hard to judge which Product Category made the largest progress over time.
Let’s see how the running volume of orders evolved.
We will use Table Calculations for that.
Let’s right-click on the count of Orders and Add a Table Calculation. We will choose the Running total.
Notice that this specific Table calculation allows us also to add a Secondary table calculation, with many options to choose from.
We will use it extensively in the exercises, but for now, let’s just stay with a basic Running total.
Let’s save this calculation back in the Data Pane and study the formula. We will call it Running orders, and we’ll click on Edit to investigate it.
Tableau is applying a classic Table calculation; Running sum. The devil is, however, in the details. Note that Tableau informs us that the result is computed Table across.
Let’s convert the time dimension to discrete months, add years to it and present it as a labeled bar chart; like this, we will see the evolution across the entire timeline.
How about the evolution view restarting each year?
With Table Calculations, it’s very easy; we will just right-click on our measure and click on Compute using “Pane (across)”. Note that Table (across) means throughout all the values of dimensions in rows, so all years and months, while Pane across restarts within each pane - so each year.
Note that nothing changed to our underlying calculation; this is because Table Calculations heavily rely on what’s on the canvas and adapt to the changes in dimensions and order.
If we moved the Product Category to the Columns, the calculation is still performed Pane across.
Let’s change the order of dimensions, collapse to year and switch back to a Table (across) calculation.
What would happen if we sorted the Product categories differently? Let’s go ahead and sort the Product Category, based on the count of Order IDs, in ascending order; that has entirely reorganized my chart!
This is due to the partitioning and addressing. Each time we add a dimension to Columns and Rows, we are adding potential partitioning fields. While in this specific example we do not partition by any dimension, as the calculation is performed across the entire dataset and ignores the partitions, we use the order of Product Categories as an addressing field, so sorting it differently directly impacts the results.
How about the window calculations? Let’s experiment by creating a new calculation; we will call it “Window count of orders”. Let’s type in window sum on that measure and see what happens!
We’ll add it to the canvas as a row measure.
Aha! With this calculation, we computed the total count of orders across the entire table. I think we know what happens if we adapt this to Pane (across) calculation!
These are the subtotals, partitioned by Product Category! Very useful!
Let’s see how we can use it further! One idea is to divide these two measures by one another; let’s do it in an ad-hoc calculation.
We will also format it as a percentage, remove all other measures, swap the order of dimensions and change it back to Pane across calculation.
We just calculated the cumulative yearly contribution rates per Product Category.
If we now sort the yearly contribution rates per Product category in descending order, clicking here, we can see that Computer accessories account for already 50% of yearly orders, with other Product Categories contributing to much smaller rates! We may as well specialize in the computer business, then!
Interesting insights!
Now let’s see how you apply the Table calculations in the exercises!
2. Let's practice!