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/
Joomla Software vs Dreamweaver Software which one is the best?
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