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

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.

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.

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

## 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:## 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.

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

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

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:

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

## 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 pale blue line shows the SUM of sales for all months while the orange line shows the 15-period moving average of sales.

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

## Post a Comment