How to Create a Control Chart in Power BI

In this article, I will be showing you how you can build a dynamic control chart in Power BI that helps your team pinpoint any outliers or processes out of control.

With companies implementing Lean Six Sigma practices, it is imperative to keep track of how processes change over time. Control charts can help the business visualize process measurements over a span of time.

Control Chart (Image from r-bar.net)

First things first for those who don’t know what exactly is a control chart.

🚩 A control chart consists of 4 main features:

1. X and Y-axis Values: The data value will be the Y-axis. This can be count of customers, count of tickets, revenue, cost, or whatever data value the business wants to measure. Time will be the X-axis.
2. The Center Line (AKA Average): This is the mean/average of the data points. It is represented with a solid line drawn in the middle section of the graph.
3. Lower Control Limit (LCL): This is found below the average and is referred to as -3 sigma line, which acts as a lower bound.
4. Upper Control Limit (UCL): This is above the average and referred to as +3 sigma line, which acts as a upper bound.

This is a control chart I created in Power BI (Image by Author)

🚩 Let’s get started by building that Control Chart in Power BI

In order to achieve the main features of a control chart, we start by creating some DAX measures:

For the Average calculation, you will simply be using the AVERAGE function. We included the ALLSELECTED function in order to make the calculation dynamic based on what the user selects in the date slicer.

AVERAGE CASES = CALCULATE(AVERAGEX(CONTROL_CHART_DATE,[TotalCases]), ALLSELECTED(CONTROL_CHART_DATE))

In order to derive the UCL and LCL, we first need to calculate the standard deviation. The syntax for standard deviation is as follows:

STDEV = CALCULATE(STDEVX.P(CONTROL_CHART_DATE,[TotalCases]),ALLSELECTED(CONTROL_CHART_DATE[DATE]))

Now that we have the standard deviation, we can input this piece inside the UCL and LCL syntax.

For UCL, we add the AVERAGE CASES measure with the STDDEV measure, then multiply by 3 (Hence, 3 sigmas).

Upper Control Limit (UCL) = AVERAGE CASES + [STDEV]*3

For LCL, we do the opposite of UCL. We subtract the AVERAGE CASES measure with the STDDEV measure, then multiply by 3.

Lower Control Limit (LCL) = AVERAGE CASES - [STDEV]*3

Create a line chart with the measures:

Once you created these measures, create a line chart. Drag LCL, UCL, and Average Cases into values. Select the Date for the axis.

The following actions should give you a view of the main components of a control chart. Don’t forget to drag in your date slicer. The date slicer will allow for users to look at the lower and upper bound limits based on historical dates.

Now for the remaining piece: adding the current data on the dashboard. You will need to create another independent date table to use for the second date slicer. These two date tables should be connected to the table where you are grabbing your main values from.

Once you created the 2nd date table, add a date slicer using this table.

Now for the fun part. Some of you may be wondering how can you get current year data to reflect on a chart with a date axis that’s using historical data?

The solution is a creative workaround. Create another line chart that uses Date 2 as the x-axis and grab the value you are analyzing as the Y-axis. If no values pop up in the line chart then pat yourself on the back because Power BI is working. You will want to edit the interactions between the date slicers and charts by selecting a date slicer and clicking Edit interactions under Format tab.

Now, you want to select the first date, in my case “Control Chart Date” and hit “none” on the 2nd date by selecting the right circle icon.

Select the 2nd date slicer and make sure that the 1st Date is not filtering it by selecting the right circle icon.

While selected on the 2nd date slicer, you will want to click filter on the last line chart you created by selecting the left icon that looks like a graph with a filter beside it.

Make sure to select “None” on the first chart with the control pieces. By having these interactions, you should see the 2nd line chart finally appear.

Now what we essentially have is 1 date slicer affecting the control chart (UCL, LCL, Average) and a 2nd date slicer affecting the chart with the total values, in this example, total cases.

Adjust and format the line charts accordingly:

So, how do you get the chart to be on top of each other? Click on the 2nd chart and format the background to be transparent so it is clearly shown on the 1st chart. Place the chart on top of the other.

Make sure to adjust both charts so the Y-axis has the same scale or else the total values may not adjust to the scaling of the control chart.

Lock both charts together to keep the format by selecting both charts with CTRL and clicking Lock Objects under view so the scaling does not get affected.

Voila! This is what the end result should look like. I am able to select 2019 and 2018 date ranges which affect the control chart piece while comparing it with my current 2020 data.

The end result is a control chart which you can compare current data with historical averages, upper control limits, and lower control limits. Because the date slicers and charts are independent of each other, you are able to compare different date ranges unlike functions such as SAMEPERIODLASTYEAR that limits users to only a comparison of last year.

Happy reporting and hope this creative solution can help other Power BI Developers have an easier time making a control chart!

—

Natalie Garces is a data analyst from the Greater Seattle Area. She received her BA in Business and MSBA from the University of Washington. She writes and is passionate about data visualization, investing, and all things tech.