MIP Logo

Creating a Dynamic Control Chart in Tableau

This post was originally published on The Data School blog between 2018 and July 2025, before our program was renamed to MIP’s Analytics Career Accelerator. References throughout this article to “The Data School” or “DS” all refer to what is now MIP’s Analytics Career Accelerator. The program, its people, and its commitment to launching outstanding analytics careers remain the same – just under a new name.

If you are having trouble viewing this article, please report it here. 

Control Charts are typically used to monitor business processes over time, or variations in products or product categories. A control chart always has a central line for the average, an upper line for the upper control limit and a lower line for the lower control limit. The simplest and fastest way to build a control chart in tableau is adding a distribution band from the analytics pane, and setting the upper and lower limits to -m.+m standard deviations from the average.

In this blog I demonstrate how to make a control chart dynamic in 6 steps. i.e : parameterizing the upper and lower limits of the chart and coloring product categories accordingly.

Control Chart of Sales and Profit by Product Subcategory ( Sample Superstore Data)

Step : 1

Find the Average Profit and Average Sales:

  1. WINDOW_AVG(sum([Profit]))
  2. WINDOW_AVG(sum([Sales]))

Step : 2

Find the Standard Deviation from the Averages:

  1. WINDOW_STDEVP(SUM([Profit]))
  2. WINDOW_STDEVP(SUM([Sales]))

Step : 3

Create a Number of Standard Deviations Parameter

Step : 4 

Find the Positive/Negative difference from the average

  1. [Fixed Average Sales]+ [Number of Stdev]*[Stdevp_Sales]
  2. [Fixed Average Sales]- [Number of Stdev]*[Stdevp_Sales]
  3. [Fixed Average Profit]+ [Number of Stdev]*[Stdevp_Profit]
  4. [Fixed Average Profit]- [Number of Stdev]*[Stdevp_Profit]

Step: 5

Add Reference Lines using the Calculations above after adding those calculations to detail.

Step: 6

Create Color Calculations

  1. Color1=  if sum([Sales])>= [Positive Difference_Sales]
    or sum([Sales])<= [Negative Difference_Sales]
    then True else False
    end
  2. Color2= if sum([Profit])>= [Positive Difference_Profit]
    or sum([Profit])<= [Negative Difference_Profit]
    then True else False
    end

and Finally add both calculations to color, and show your parameter control.

 

Share this post