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.