Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 35 ยท Advanced Analytics

Reference Lines, Bands and Distributions in Tableau

Add reference lines, bands, distribution bands and box plots in Tableau, choose the scope, and recompute every line with pandas to see what it means.

A bar chart of sales per region tells you which region is bigger. It does not tell you whether "big" is good. A reference line adds the missing yardstick: the average, a target, the 90th percentile. A band shades a range between two values, and a distribution band shades statistical ranges such as "within one standard deviation". Together they turn a plain chart into a judgment: above target, below average, an outlier.

Tableau is a graphical tool, so the steps are instructions for the desktop application. The runnable examples are stand-ins: pandas and SQL calculations that produce the same numbers the lines are drawn at, so you can check them and understand them.

Adding lines and bands

There are two ways to start:

  1. Analytics pane: drag Reference Line, Reference Band, Distribution Band or Box Plot onto the view. A drop zone offers three scopes: Table, Pane or Cell.
  2. Right-click an axis > Add Reference Line (or Band, Distribution Band, Box Plot).

The dialog has four things to choose:

  • Scope: Entire Table draws one line across all panes, Per Pane draws one for each pane, Per Cell one for each cell of a view with a dimension on Rows and Columns.
  • Value: which aggregation of which field. A constant, or SUM, AVG, MEDIAN, MIN, MAX, a percentile, and so on.
  • Label: Value, Computation, Custom, or none. A label such as "Target 450K" turns a stray line into a message.
  • Formatting: line style, colour, shading above and below the line, and a tooltip.

Four ways to add context

Reference line One value, such as average or target.
Reference band Shade between two values.
Distribution band Percentages, percentiles, std. dev.
Box plot Median, quartiles and whiskers.
1 / 2

Reference lines

The simplest line is a constant (a target you type) or an aggregate. Suppose you chart monthly profit per region. Dragging an average reference line with Entire Table scope gives one horizontal line: the average of all marks in the view. With Per Pane, each region's pane gets its own average. These are two different numbers answering two different questions: "is this month above the overall average?" and "is it above this region's average?".

Python Average per table vs per pane

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that East's month 1 (120) is above the table average (102.5) but below East's own average (125). The scope decides which story you tell.

Use the Computation label and a plain-language title ("Company target") so viewers never have to guess what a line represents.

Lines with a percentile or a constant

In the dialog, choose Value: Percentile and type 90 to draw the 90th percentile line, handy for "top decile" thresholds. A Constant is right for targets that do not come from the data. A Parameter (see the Calculations chapter) lets the viewer change the target with a slider.

Tableau's percentile uses linear interpolation between neighbours, like numpy.percentile with the default method, so you can verify a line in pandas:

Python Percentile and quartile lines

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Reference bands

A band shades the area between two values: from a minimum to a maximum, from one constant to another, or from the 25th to the 75th percentile. Typical uses are an acceptable operating range ("temperature must stay between 18 and 24"), a target zone, or the "normal" range of last year. Choose Reference Band, set the From and To values (each can be an aggregation, a constant or a parameter), and set a fill colour with some transparency so the marks stay visible.

Distribution bands

A distribution band shades several ranges at once, based on the data distribution. In the dialog, Computation can be:

  • Percentages: for example bands at 60%, 80% and 100% of the average.
  • Percentiles: bands at the 25th, 50th and 75th percentile.
  • Quantiles: split into equally sized groups (quartiles, quintiles...).
  • Standard Deviations: bands at plus/minus 1, 2 or 3 standard deviations from the mean.

Standard-deviation bands are an easy outlier detector. In a roughly bell-shaped distribution about 68% of values lie within one standard deviation of the mean and about 95% within two. A mark outside the two-sigma band deserves a second look. Tableau uses the sample standard deviation (n-1), as does pandas std().

Python Standard-deviation band as an outlier test

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Here the one unusual day stands out. Beware of a trap: an outlier also inflates the mean and the standard deviation, which widens the band. With a few extreme values consider a median-based rule (see box plots below).

Box plots

A box plot is a special reference distribution. Drag Box Plot onto a view with a dimension and a measure at the detail level (for example each order). Tableau draws:

  • the box from the 1st to the 3rd quartile (the middle half of the data),
  • a line at the median,
  • whiskers that reach to the furthest value within 1.5 times the interquartile range (IQR) of the box,
  • dots beyond the whiskers: the outliers.

You can change the whisker rule in the Edit Reference Line dialog (for example to min/max). The IQR rule is easy to reproduce, and here it is in SQL, because every database can compute this check too. SQLite has no percentile function, so the stand-in below computes the quartiles with window functions on a tiny table.

SQL IQR outlier rule in SQL

Press Run (or Ctrl + Enter) to see the result here.

The quartiles here are a simple rank-based approximation. Tableau offers two quartile methods (linear interpolation and the median of halves), so its numbers can differ slightly from this query on small data sets, and from pandas quantile. The principle, fences at 1.5 IQR beyond the box, is the same.

Practical advice

  • Name every line. A line without a label is decoration.
  • Match the scope to the question. Per-pane lines compare marks with their own group; table lines compare with everybody.
  • Don't draw the average over a skewed measure. Order values and salaries are skewed, so the median is more representative.
  • Shade sparingly. Two bands per chart is plenty.
  • Use a parameter for a target so business users can adjust it without editing the workbook.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You want each region's marks compared with that region's own average. Which scope do you choose?

    Per Pane draws one reference line per pane. In a view with Region on Rows, each pane is one region.
  2. 2. In a Tableau box plot, what do the whiskers show by default?

    By default whiskers reach the furthest values within 1.5 x IQR. Points beyond them are drawn as outliers.
  3. 3. Why can a two-standard-deviation band miss an extreme outlier?

    A huge value raises the standard deviation, so the band can grow large enough to contain it. A median/IQR rule is more robust.
  4. 4. Which reference-line value would you use for a fixed company target of 450,000?

    A constant is a typed value that does not come from the data. Use a parameter if viewers should be able to change it.

Summary

  • Reference lines, bands, distribution bands and box plots add the yardstick that turns marks into a judgment.
  • The scope (table, pane, cell) changes the computed number, so choose it deliberately.
  • Distribution bands can be percentages, percentiles, quantiles or standard deviations; box plots use the 1.5 x IQR whisker rule.
  • Always label lines, prefer medians for skewed data, and use parameters for targets.
  • The pandas and SQL stand-ins reproduce the numbers; Tableau's quartile method may differ slightly on small data.

Next: Clustering and Statistics in Tableau