How to Create a Control Chart

Standard Deviation is used to show how far away data is from the mean. Low deviation means data are clustered around the mean, and high standard deviation indicates data are more spread out.

A control chart uses standard deviations above and below the mean. These are displayed as a band around the mean with outliers are identified using color.

In the example I walkthrough below, I used StoresSales, looking at when sales spiked for different segments across the year. I have also incorporated control parameters, where users can adjust the standard deviation/ Grey Band on the graphs, as well as the outlier color indicator.

Some insights at the end and for my workbooks here.

Step One: Make the base chart.

For this, we want to see the sales over the months for segments. So drag the dimension 'Segment' and the measure 'Sales' (average) onto the row shelf and dimension 'Order Date' (Date) on the column.

Step Two: Layer to create two charts.

Duplicate and dual-axis the measure, not forgetting to 'synchronize the axis’ and hide the right header, to create a dot and a line on the same chart.

Tidy the chart a little by hiding the second, unnecessary header and change the type of the chart on top from line to circle.

To make the variation in the data clearer, edit the right axis and untick show zero.

Drag on the average line from the analytics pane. We want this to be for each pane, rather than for the table. This is because we want an average line per segment.

Step Three: Create the control parameter

Create a control parameter, ‘Choice of SD ’, as an integer with a range of 1 to 3 and a step of 1. Right-click on the control parameter pill to show it in the view.

Step Four: Upper and Lower Bound Calculated Field

Create 2 calculated fields to act as the upper and lower bounds of the graph bands. These will interact with the control parameter. The calculated fields use window averages for both the sales and the standard deviation. It uses the following formula but remember to have the correct + and – in the middle for upper and lower:

WINDOW_AVG(AVG([Sales]))+WINDOW_STDEV(AVG([Sales]))*[Choice of SD].

We want to use the SD for sample not for population unless you know you got the whole population.

Step Five: Add the Reference Band

Drag the reference bands from the analytics pane onto the chart. Set the new calculated fields as from (lower) and to (upper).

Step Six: Color the Control Chart

Let's get a little fancy and more detailed by coloring the outliers. Create a calculated field to ‘group’ the values on the chart by whether they are outside or inside the reference bands.

Put the color shelf on the circle sales rather on "ALL".

And Voila! a Control Chart

Final outputs, using the slider bar to increase the multiples of standard deviation away from the mean.

Useful Insights:

A control chart helps create really useful insights for a company. Here we can see that over October and in March sales spike for Home Office– probably because of people doing WFH after the pandemic.