Dataset 008 – Control is not an Illusion

Traditional Analytics practices are often very siloed, a major asset that Power BI brings to the table is the ability to combine ideas and methodology, this invariably opens new opportunities.
This video goes through how you can use Six Sigma style Control Charts within Power BI to rapidly assess identify days that where volume isn’t within band, this method works with count(), sum(), Average() etc… It can also be replicated across multiple dimensions, not just date as we do in this example. We’ll also show you how to work elements of the technique into a Calculation Group so it can be quickly used to analyse different areas of your business as well.
Control Charts
Incidents =COUNTROWS(incidents)
Mean =CALCULATE (
AVERAGEX ( ‘Calendar’, [Incidents] ),
ALLSELECTED ( ‘Calendar’ )
)
StandardDeviation =CALCULATE (
STDEVX.P ( ‘Calendar’, [Incidents] ),
ALLSELECTED ( ‘Calendar’ ) )
Lower Limit =[Mean] – [StandardDeviation]
Upper Limit =[Mean] + [StandardDeviation]
LT Lower Part =
IF (
[Incidents]<[Lower Limit],
[Incidents])
LT Lower Full =
IF (
[Incidents] >=[Lower Limit],
[Lower Limit]
)
STD =
IF ([Incidents]>[Lower Limit] && [Incidents] <= [Upper Limit],
[Incidents]-[Lower Limit],
If (
[Incidents] > [Lower Limit],
[Upper Limit] – [Lower Limit]
)
)
GTUpper =
IF (
[Incidents] > [Upper Limit],
[Incidents] – [Upper Limit]
)

Calculation Groups

Current =SELECTEDMEASURE()
AVG =CALCULATE (
AVERAGEX ( ‘Calendar’, SELECTEDMEASURE () ),
ALLSELECTED ( ‘Calendar’ )
)
STDEv =VAR Mean = CALCULATE (
AVERAGEX ( ‘Calendar’, SELECTEDMEASURE()),
ALLSELECTED ( ‘Calendar'[Dates] )
)
VAR Deviation = Calculate (STDEVX.P ( ‘Calendar’, SELECTEDMEASURE()),
ALLSELECTED ( ‘Calendar'[Dates] ) )
RETURN
Deviation
Lower =VAR Mean = CALCULATE (
AVERAGEX ( ‘Calendar’, SELECTEDMEASURE()),
ALLSELECTED ( ‘Calendar'[Dates] )
)
VAR Deviation = Calculate (STDEVX.P ( ‘Calendar’, SELECTEDMEASURE()),
ALLSELECTED ( ‘Calendar'[Dates] ) )
RETURN
Mean – Deviation
Upper =VAR Mean = CALCULATE (
AVERAGEX ( ‘Calendar’, SELECTEDMEASURE()),
ALLSELECTED ( ‘Calendar'[Dates] )
)
VAR Deviation = Calculate (STDEVX.P ( ‘Calendar’, SELECTEDMEASURE()),
ALLSELECTED ( ‘Calendar'[Dates] ) )
RETURN
Mean + Deviation
ExceleratorBI Stack idea – https://exceleratorbi.com.au/six-sigma-control-charts-in-power-bi/

 

2 thoughts on “Dataset 008 – Control is not an Illusion”

    • Wow, that’s a question. I have used both in the past and I would say it typically will come down to what you’re most comfortable with.

      Ross

%d bloggers like this: