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.
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.
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:
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.
created a disconnected table to capture the user choice of # of std dev from the mean
created the values for the upper line - mean plus (multiplier selected in 2 above * stdev sample (s) )
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:
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?
