Control Charts in Power BI

A control chart is a line chart with added context; it shows a value over time, along with upper and lower control limits. These limits help show whether a value is behaving within its normal range, or whether a point looks unusually high or low.

In this example, I built a control chart in Power BI using monthly sales.

The Basic Idea

The chart shows:

Element	Purpose
Total Sales	The monthly value being tracked
Upper Line	The upper control limit
Lower Line	The lower control limit
Coloured Points	Months above or below the limits

Total Sales

First, I created a simple total sales measure.

Total Sales =
SUM(Orders[Sales])

This is the main measure shown on the chart.

Date Table

I also used a date table.

_date =
ADDCOLUMNS(
    CALENDARAUTO(),
    "Month Year String", FORMAT([Date], "yyyy-MMM"),
    "Month Year", EOMONTH([Date], 0)
)

For this chart, I used the Month Year field on the x-axis.

Using a date-based month field helps keep the axis in the correct order, as using a string would add problems with sorting happening alphabetically or by descending Sales by default.

Standard Deviation Parameter

I created a parameter so the user could choose how many standard deviations to use for the control limits. The generated table looked like this:

+/- Std Deviations =
GENERATESERIES(0.5, 3, 0.5)

This creates values from 0.5 to 3, increasing by 0.5.

Power BI also creates a selected value measure for the parameter. In my calculations, that measure is:

[+/- Std Deviations Value]

This lets the user make the control limits narrower or wider.

Upper Control Limit

The upper line is calculated as:

average monthly sales + selected number of standard deviations

Upper Line =
VAR avgSales =
    AVERAGEX(
        ALL(_date[Month Year]),
        [Total Sales]
    )

VAR std_Dev =
    STDEVX.S(
        ALL(_date[Month Year]),
        [Total Sales]
    )

VAR UpLine =
    avgSales + [+/- Std Deviations Value] * std_Dev

RETURN
    UpLine

Lower Control Limit

The lower line uses the same logic, but subtracts the selected number of standard deviations.

Lower Line =
VAR avgSales =
    AVERAGEX(
        ALL(_date[Month Year]),
        [Total Sales]
    )

VAR std_Dev =
    STDEVX.S(
        ALL(_date[Month Year]),
        [Total Sales]
    )

VAR LowLine =
    avgSales - [+/- Std Deviations Value] * std_Dev

RETURN
    LowLine

Why Use `ALL`, `AVERAGEX` and `STDEVX.S`?

The control limits need to be based on the monthly sales values across the chart, not just the current month being plotted.

This part gets the full list of months, ignoring the individual month currently being drawn on the line chart:

ALL(_date[Month Year])

Then AVERAGEX calculates total sales for each month and averages those monthly totals.

STDEVX.S does the same kind of iteration, but calculates the standard deviation of the monthly totals instead. This means that the measures are not averaging individual order rows, they are calculating the average and standard deviation of monthly sales.

Colour Calculation

To highlight points outside the control limits, I created a colour calculation.

Control Colour Calc =
IF(
    [Total Sales] > [Upper Line],
    "MidnightBlue",
    IF(
        [Total Sales] < [Lower Line],
        "Salmon",
        "LightGrey"
    )
)

This checks whether the current month’s sales are above or below the limits.

If sales are above the upper line, the point is coloured MidnightBlue.
If sales are below the lower line, the point is coloured Salmon.
Otherwise, the point is coloured LightGrey.

Conditional Formatting Trick

One slightly awkward part of this build was applying conditional formatting to the line chart markers, as the option for conditional formatting isn't available for this chart type.

The handy workaround I used was:

1. Temporarily change the line chart into a bar or column chart.

2. Apply conditional formatting to the bars using the colour calculation as the formatting field.

3. Change the visual back to a line chart.

The conditional formatting carries across, so the line chart points are coloured based on whether they fall inside or outside the control limits.

Final Thoughts

Control charts are a useful way to add more context to a time series.

Instead of only showing whether a value has gone up or down, they help show whether a point looks unusual compared with the normal pattern.

In Power BI, the main ingredients are:

a measure to track
a date table
a standard deviation parameter
upper and lower control limit measures
a colour measure for conditional formatting

It is a small amount of DAX, but it makes the chart much more useful for spotting values that may need further investigation. I hope this tutorial will be helpful if you need to create a control chart in Power BI!

Author:

Holly Andersen

View Profile