Top 10 Tableau table calculations

Here are 10 powerful examples of Tableau's table calculations. Each example contains a live example and instructions in a tabbed view. You can download any workbook for a deeper look. Table calculations are transformations you can apply to the values in a visualization. They are a special type of calculated field that computes on the local data in Tableau based on what is currently in the view. Therefore, dimensions and measures that are filtered out of the visualization are not considered in the results.

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.

For more information, watch this Introduction to Table Calculations.

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.

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 first 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.

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 a Secondary Calculation in Tableau and it can be done without even writing a formula.

The first pass is to calculate a running total of sales over time by segment. The second pass is to calculate the running total of each segment as a % of total over time.

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 our third largest seller. We can also see that there has been a lot of volatility in the purchase of fax machines and copiers.

A classic bump chart. This shows the sales position of each product computed with a simple Rank Table Calculation and some advanced settings.

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.

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 weigh 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.

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

Diverse scenarios such as retail or intelligence 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 zero. A dot above zero 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.

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 your end user can choose how many periods they 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 (user-defined) periods.

You may be interested in seeing the difference in quarterly sales from that year’s average. Here we show the difference from this year’s average, the overall average, and the total number of orders.

Learn more about types of table calculations and table calculation functions.
 

Editor’s Note: This post was updated in November 2019, based on the original content by Ellie Fields.