Level: intermediate
I have received a couple of requests for help during my live training classes and online training classes on how to build a Six Sigma control chart.. Now I don't pretend to be a Six Sigma black belt, but I know how to write DAX and I know how to use Power BI. So here it goes. This is what I'm going to build.
The method
As usual, I used the database of Adventure Works in this post. Then I used DAX formulas and Power BI visual items to get to this graph of six sigma. These 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 get the mean (average) of sales per day.
Average Sales =
CALCULATE(
AVERAGEX('Calendar', [Total Sales]),
ALLSELECTED('Calendar')
)
Note that I used the feature AVERAGEX iterating over the Calendar table to get the average sales during the selected days instead of the average per transaction.
3. Then, 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. Then I created a combo box – lines show mean and control limits (LCL y 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 shows the distribution of sales over the selected period of time.. The part of the columns below LCL, above UCL and between LCL and UCL should be displayed in different colors. To get this, I segment 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 got to each of these DAX formulas that resulted in the interactive six sigma control chart as shown at the beginning of this post, see the video below.
And here is the sample file that is used.
