# Standard Deviation

Hi folks,

I’m trying to create a chart that uses Standard Deviation so I can create a line that has an Upper Limit and a Lower Limit based on a previous period (they are 8 month periods and found in my date table as 8 month fixed period)

In the chart the Mean Prev is the Mean of Fleet Type AA defects for the previous 8 month period and it will stay static for 8 months.

I need to create an Upper Limit line and Lower Limit line based on Standard Deviation from the previous 8 months so we can view when Fleet Type AA breaches the Upper or Lower limits.

Any help or assistance on this would be great. If I’ve not explained things clearly then let me know.

Thanks David

SPC File v1.4.pbix (177.0 KB)

How many standard deviations from the mean do you want the upper and lower limits to extend?

• Brian
1 Like

I had been given a rough Excel template and it used 2.66 as a multiplier in it, does that make sense?

If not, is it easy to set up for 1 standard deviation and then I can change to 2 if necessary?

Test Data SPC.xlsx (19.6 KB)

The excel template does not have the offsets set up correctly, it was for illustration purposes.

It really depends what questions you’re trying to answer. If your data is roughly normally distributed, 1 std dev from the mean will contain approximately 68% of your data, 2 std devs 95% and 3 std devs 99.7%. Thus, more than 3 std devs from the mean is often a criteria used for defining and identifying outliers.

I think we can definitely set this up dynamically with a what if parameter.

• Brian
1 Like

Thank you @BrianJ, much appreciated, trying to get this to work has been really stressing me out this week

Not to worry - we’ll get this worked out. I’m away from the computer now, but will give this a go later today.

• Brian
1 Like

First off, kudos for a perfect forum request = clear explanation of the problem, PBIX file, underlying data file and mockup of desired result. Definitely makes it easier for us to provide prompt, specific support.

I feel like you did all the hard work on this one, and left me with the fun stuff.

Heres what I did:

1. created a measure to calculate the sample standard deviation of the mean for the rolling 8 month periods. Leveraged off the existing Mean Prev measure by just replacing AVERAGEX with STDEVX.S.
2. created a disconnected table to capture the user choice of # of std dev from the mean
3. created the values for the upper line - mean plus (multiplier selected in 2 above * stdev sample (s) )
4. created the values for the lower line - mean minus (multiplier selected in 2 above * stdev sample (s) ), but with a floor condition that prevents the values from going negative (can’t have negative defects). Negative values just depicted as 0.

Here’s what it looks like put together (s and 2s selected respectively, to show the effect of the floor condition:

I hope this gets you what you need. Full solution file attached.

4 Likes

That’s awesome @BrianJ, thank you! Works beautifully and setting the floor condition was something I hadn’t thought about.

One question, that I should’ve thought of before. If my users wanted to filter the date down to, say the last 6 months, is there a way to still allow the calculations to function properly?

@Melissa’s date table offsets make that a snap. Just set the filter pane as such:

• Brian
1 Like

Ah, that date table is so great! Magic!

p.s. Aren’t you up a bit early @BrianJ?

Actually, I’m up a lot too late…

1 Like

Hahahahaha!

1 Like