Top 10 Tableau table calculations

Here are 10 powerful examples of Tableau's table calculations. Most require writing a simple formula of some kind. Each example contains a live example and instructions in a tabbed view. You can download any workbook for a deeper look.
In order to view and recreate these table calculations, you will need a copy of Tableau Desktop. You can get a free 14-day trial here.

Table calculation basics

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 do what it sounds like they do: They partition your data into separate buckets, each of which is acted on by the calculations.
Addressing fields define the “direction” that you want your calculation to take.
In the example to the right, we have a running total of sales. Segment is a partitioning field, so a running total is calculated for both segments-- consumer and corporate. Date is the addressing field, so sales are summed over time for both segments.

1. Percent change from a reference date

With table calculations, you can calculate the percent change from an arbitrary value. Suppose you are interested in a portfolio of stocks, and want to evaluate the relative performance of them from a point in time. To do this, you need to set an “investment date” and normalize them to the same point in time, with lines showing percentage change. You adjust the reference date using the slider.

Using a parameter for the reference date and the WINDOW_MAX function to retrieve the close price on the reference date, you can compute the relative return of stocks.

2. Common baseline (Toy Story)

You may want to see data from a common starting point rather than over an absolute timeline. For example, here are the box office receipts for the three Toy Story movies. It’s much easier to compare them if you look at gross receipts by week since the opening date:

Tableau’s INDEX() function allows you to easily compute the number of weeks since opening. In this case you partition by Movie and address by days.

3. Percent-of-total sales over time (multi-pass aggregation)

It's common to want to perform two table calculations at once. For example, it can be interesting to see how a segment has grown or shrunk in importance to the company over time. To do this, you must first compute running sum of sales by segment over time, then look at that as a percent of all sales over time. This is also called multi-pass aggregation and it can be done without even writing a formula in Tableau.

The 1st pass is to calculate a running total of sales over time by segment. The 2nd pass is to calculate the running total of each segment as a % of total over time.

4. Preserving ranking even while sorting

Here we need to see the rank of a product within a month and year, and then show how its ranking changes across time. To achieve this, we create a bump chart, which shows change over time as a line chart. On the left, we can see how copiers and fax machines have gone from a poorly performing product to presently being out 3rd largest seller. We can also see that there has been a lot of volatility in the purchase of fax and copier machines.

A classic bump chart. This shows the sales position of each product computed with a simple Rank (index()) calculation and some advanced settings.

5. Running total

You need to monitor the number of active support cases at your call center, or stock on shelves. But the system doesn’t record the rolling total of active cases and you need to derive it. This is equal to # of Cases at Day Open + New Cases + Reopened Cases – Closed cases.
On the surface this is a simple calculation. However, the daily opening position is derived from the prior day close, which, in turn, is derived from that day’s opening position. This creates a circular reference of calculations.

We use WINDOW_SUM to calculate running totals and determine each day's closing amount.

6. Weighted average

Data such as test scores or order priority lends itself to analysis by weighted average. Perhaps you are looking at the average priority of all orders across product types and want to weight that priority by order volume, so that higher-volume products receive a higher priority score. You might use that weighted average priority score to optimize your supply chain for high-volume, high-priority products. Here we do just that using Superstore sales data:

Here we again use WINDOW_SUM to calculate a weight for each category and then apply that to the priority score.

7. Grouping by a calculation

If you are managing a company's shipping operations you may be interested in which products’ shipping costs are higher than average. In Tableau 6, you can compute the average across a window and use that in a calculation to group and color values.

8. Number of incidents over a moving range

Diverse scenarios such as retail, intelligence, or border control often involve the number of times an event has occurred within a window. For example, one suspicious event may be an anomaly, but if it happens more than n times in x days, then it warrants investigation.

The dots show the number of times an alert or alarm has been raised – typically 0. A dot above 0 shows that an alert was triggered on that day, and a bar shows that the alarm has been triggered more than n times in x days. The user can right click and show data for both dots and bars.

9. Moving average over variable periods

You have computed the moving average for sales for all months by using the quick table calculation functions in Tableau, but would now like to extend it so that you can choose how many periods you want to average.
The pale blue line shows the SUM of sales for all months while the orange line shows the 15-period moving average of sales.

The combination of a Parameter and a customized Quick Table Calculation for moving average lets us average over variable periods.

10. Difference from average by period

You may be more interested in seeing the difference in quarterly sales from that year’s average than the absolute number. Here we show both the difference from the year’s average and the absolute number of orders.

Want to try some of these table calculations with your own data? Try Tableau Desktop for free.

Via tableau