Level: Intermediate – I have had a couple of requests for help during my live training classes and online training classes on how to build a Six Sigma Control Charts in Power BI. Now I am not professing to be a Six Sigma black belt, but I do know how to write DAX and I do know how to use Power BI. So here goes. This is what I am going to build.
The Method to create Six Sigma control charts in Power BI
As usual, I used the Adventure Works database in this article. I then used DAX formulas and Power BI visuals to arrive at this six sigma chart. Here are the steps I followed to produce the result.
1. I created a DAX formula for total sales.
Total Sales = SUM(Sales[ExtendedAmount])
2. Then I created a DAX formula to obtain the mean (average) of the sales by day.
Average Sales =
CALCULATE(
AVERAGEX('Calendar', [Total Sales]),
ALLSELECTED('Calendar')
)
Note that I used AVERAGEX function iterating over the Calendar table in order to get the average sales over the selected days instead of the average per transaction.
3. Next I calculated the LCL and UCL by adding/subtracting one standard deviation, as follows.
Std Dev =
CALCULATE(
STDEVX.P('Calendar', [Total Sales]),
ALLSELECTED('Calendar')
)
= [Average Sales] - [Std Dev]
= [Average Sales] + [Std Dev]
4. I then created a combo chart – the lines show the average and control limits (LCL and UCL) and the columns show the total sales values. I used a slicer to filter the time period.
The idea is to create an interactive dynamic chart that displays sales distribution over the selected time period. The part of the columns below LCL, above UCL, and between LCL and UCL are to be displayed with different colours. To get this, I had segmented the columns with the following DAX formulas.
Part Lower Bar = IF([Total Sales] < [LCL], [Total Sales])
Full Lower = IF([Total Sales] >= [LCL], [LCL])
Middle =
IF(
[Total Sales] > [LCL] && [Total Sales] <= [UCL],
[Total Sales] - [LCL],
IF([Total Sales] > [LCL], [UCL] - [LCL])
)
Upper = IF([Total Sales] > [UCL], [Total Sales] - [UCL])
To understand how I arrived at each of these DAX formulas that resulted in the interactive six sigma control chart in Power BI as given at the beginning of this article, view the video below.
And, here is the sample file that is used.
This is wonderful sir. Thank you for explaining so clearly.
Hi Matt
is it possible to amend you’re excellent work above to do the following – as opposed to using STD
The true calculation uses something called the median moving range (Upper Limit: Multiply MEDIAN Moving Range by 3.14 and add product to average)
and
(Lower Limit: Multiply MEDIAN Moving Range by 3.14 and subtract product from average)
and in order to calculate the Median Moving Range you need to find the differences between each pair of successive values; then sort the differences in numerical order; then identify the middle value.
regards
The hardest part of these blogs is getting the test data organised and in this case also understanding what is required. If you help with the prep, I will update the article. Here is the DB. https://exceleratorbi.com.au/supercharge-power-bi-book/#download
If you take a sample of this data, load it into Excel and show exactly the way to do the calculation and send it to me, I will take a look with the view to update this article.
Just came by this thread looking for PowerBI tools for typical DMAIC/Six Sigma type statistical visualizations. Matt did you ever come up with a solution that properly sets control limits at plus and minus 3*SD from the mean automatically based on data?
To the community, have you seen any 3rd party tools that produce actual control charts with the statistical tests for special cause run automatically in the background (ala JMP, Minitab, SigmaXL, etc)??
Thx!
My demo uses the SD based on the selected data. You could make that 2* or 3* if needed by simply changing the formula. Regarding tools, have you looked at R? I don’t know if it will do what you need, but I understand is is pretty capable, and it integrates nicely with Power BI
Hi Matt, Great demo. I am having anissue rolling my control chart into weekly and monthly views as opposed to a daily view. What changes do I need to make to get the mean and U/L CL s to show based on weekly and monthly data?
I guess you would just change the calculation to work across the unique months. Something like this.
Std Dev =
CALCULATE(
STDEVX.P(VALUES(‘Calendar'[Month ID]), [Total Sales]),
ALLSELECTED(‘Calendar’)
)
Power pivot for Excel can do exactly the same thing. Otherwise you can just use Excel formulas in cells
I need this exact functionality but instead with Excel… is this possible?
Matt, Really nice video and very helpful I am still unable to gather the concept per. my requirements. Will you be able to share the Power BI file so I can understand in more details how the control charts were created?
Whilst this is a really nice looking demo, it sort of undermines the whole principle of control charts. The concept is that we ‘set’ control ‘limits’ and leave them, until signals in the data (trends) indicate that investigation should take place. Because the limits are set based on historical data, they indicate a level of process predictability.
If these limits can move based on date they fundamentally contradict the concept of ‘control’ limits – you want them set, not moving and only change them based on triggers from your data. I believe this is one of the fundamental reasons people have issues with control charts
Thank you!!
Amazing!
Amazing!
Nifty, thanks.
Nifty, thanks.
Or you could use this XmR custom visual: https://github.com/tcd1nc/PowerBI-Control-Chart
Or you could use this XmR custom visual: https://github.com/tcd1nc/PowerBI-Control-Chart
This would not be considered an acceptable method of creating control limits for an x bar chart. Nor would the use of a bar chart be a good choice for identifying trends and assignables causes that’s aren’t single movements beyond the control limits (something that’s going to happen quite regularly with a 1s limit).
This would not be considered an acceptable method of creating control limits for an x bar chart. Nor would the use of a bar chart be a good choice for identifying trends and assignables causes that’s aren’t single movements beyond the control limits (something that’s going to happen quite regularly with a 1s limit).
Very interesting. thanks Matt
Very interesting. thanks Matt
This will be useful in testing boundaries of inventory levels.
I’ll give it a try. Thanks Matt.
This will be useful in testing boundaries of inventory levels.
I’ll give it a try. Thanks Matt.
Very clever. The average sales over selected time calculation is an often used formula for many business scenarios. I’ve seen it more usually applied with percentage growth/decline calculations to identify entities that are above the average and those that pull the average down (and require further attention).
Very clever. The average sales over selected time calculation is an often used formula for many business scenarios. I’ve seen it more usually applied with percentage growth/decline calculations to identify entities that are above the average and those that pull the average down (and require further attention).