Process Improvement Measures (Cp, Cplower, Cpupper, Cpk, Cpm, Cpkm) 02-21-2018 20:54 PM

[Greg_Deckler]

Given some sample data:

MeasureValueLimitUpperLimitLowerIndexTarget

0.05	0.05	0	0	0.03
0.04	0.05	0	1	0.03
0.04	0.05	0	2	0.03
0.05	0.05	0	3	0.03
0.04	0.05	0	4	0.03
0.04	0.05	0	5	0.03
0.05	0.05	0	6	0.03

Presents common process improvement measures as documented here: https://en.wikipedia.org/wiki/Process_capability_index

Cp = 
VAR sigma = STDEV.S(Process[MeasureValue])
RETURN (MAX(Process[LimitUpper])-MIN(Process[LimitLower]))/(6*sigma)

Cplower = 
VAR sigma = STDEV.S(Process[MeasureValue])
VAR estimatedMean = AVERAGE(Process[MeasureValue])
RETURN (estimatedMean-MIN(Process[LimitLower]))/(3*sigma)

Cpupper = 
VAR sigma = STDEV.S(Process[MeasureValue])
VAR estimatedMean = AVERAGE(Process[MeasureValue])
RETURN (MAX(Process[LimitUpper])-estimatedMean)/(3*sigma)

Cpk = 
VAR sigma = STDEV.S(Process[MeasureValue])
VAR estimatedMean = AVERAGE(Process[MeasureValue])
VAR Cplower = (estimatedMean-MIN(Process[LimitLower]))/(3*sigma)
VAR Cpupper = (MAX(Process[LimitUpper])-estimatedMean)/(3*sigma)
RETURN MIN(Cplower,Cpupper)

Cpm = 
VAR sigma = STDEV.S(Process[MeasureValue])
VAR estimatedMean = AVERAGE(Process[MeasureValue])
VAR Cp = (MAX(Process[LimitUpper])-MIN(Process[LimitLower]))/(6*sigma)
VAR denom = SQRT(1+POWER((estimatedMean - MAX(Process[Target]))/sigma,2))
RETURN DIVIDE(Cp,denom)

Cpkm = 
VAR sigma = STDEV.S(Process[MeasureValue])
VAR estimatedMean = AVERAGE(Process[MeasureValue])
VAR Cplower = (estimatedMean-MIN(Process[LimitLower]))/(3*sigma)
VAR Cpupper = (MAX(Process[LimitUpper])-estimatedMean)/(3*sigma)
VAR Cpk = MIN(Cplower,Cpupper)
VAR denom = SQRT(1+POWER((estimatedMean - MAX(Process[Target]))/sigma,2))
RETURN DIVIDE(Cpk,denom)

eyJrIjoiNmMzZDc2MTgtZWViMS00NWU1LTk1YmYtYTc3ZmQzZDVmNjRlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[vincentakatoh]

@Greg_Deckler, 

Check with a statistician, when calculating Cp/Cpk, my formula for Sigma is incorrect. DAX STDEV.S (also called sigma) is used for Pp/PpK and not Cpk. 

For Cpk, sigma is calculated using Moving Range (MR). In particular, for Individual Moving Range, Sigma = MRbar/1.128. 1.128 is a constant. 

Help needed: Can help provide DAX to calculate the column "Moving Range"? Moving range is the absolute difference between adjacent MeasureValue.

[vincentakatoh]

Hi @Greg_Deckler, 

Added link to sample pbix file. Need help on DAX to calculate Moving Average (MR).

https://1drv.ms/u/s!ArjVwEnHONXNghspzwtVdKi9T6KS

Once done,

1) calculating MRbar, CPk and Cp are easy. 

2) ImR Chart: Creating ImR chart will be easy. Just 2 line charts

http://blog.minitab.com/blog/understanding-statistics/how-create-and-read-an-i-mr-control-chart

[vincentakatoh]

hi @Greg_Deckler, 

Wanted to share results of my work with help from  @Zubair_Muhammad and yourself. Thank again. 

Objective: Create Individual Moving Range Chart (simplest of SPC chart) using Power BI. 

- Calculate: Cp, Cpk, Pp & Ppk

- Calculate: Moving Range (MR) and Sigma=MRbar/1.128

https://1drv.ms/u/s!ArjVwEnHONXNgh2-T7FtnCPcmkYg

[igor722]

Hello. I am very new to this.

But is it possible to create a Data source for this template?

Greetings

[Greg_Deckler]

@vincentakatoh - A statistician I am not! So, is the only change that is required to the sigma calculation or is there other things that are incorrect? If you can help me get the equations correct I will update the post or see if I can remove it and let you post the correct Quick Measures to the gallery!

[vincentakatoh]

@Greg_Deckler, 

Below is how I calculae Cpk. Calculations for Cpk is when Sample=1. 

Step 1:
EarlierTime = CALCULATE ( MAX ( QLMS[TimeSPC] ); FILTER ( ALLSELECTED ( QLMS[TimeSPC] ); QLMS[TimeSPC] < SELECTEDVALUE ( QLMS[TimeSPC] ) ) ) 

Step 2: 
MovingRange = 
IF(ISBLANK(QLMS[EarlierTime]);blank();(
VAR EarlierTime = CALCULATE ( MAX ( QLMS[TimeSPC] ); FILTER ( ALLSELECTED ( QLMS[TimeSPC] ); QLMS[TimeSPC] < SELECTEDVALUE ( QLMS[TimeSPC] ) ) ) 
VAR EarlierMeasureValue = CALCULATE ( SUM ( QLMS[MeasureValue] ); QLMS[TimeSPC] = EarlierTime ) 
RETURN ABS ( EarlierMeasureValue - SUM ( QLMS[MeasureValue] ) ) ))

Step 3:
MRBar = AverageX(ALLSELECTED(QLMS[TimeSPC]);[MovingRange])

Step 4: (1.128 constant applies when sample size=1)
Sigma = [MRBar]/1.128

Step 5: 
Cp = (MAX(QLMS[Limit_USL])-MIN(QLMS[Limit_LSL]))/(6*[Sigma])

Step 6:
Cpk = 
VAR CpU = (MAX([Limit_USL]) - [Mean])/(3*[Sigma])
VAR CpL = ([Mean] - MAX([Limit_LSL]))/(3*[sigma])
RETURN MIN(CpU;CpL)

[Anonymous]

Hi Vincent, I'm trying to use your measures but I'm getting stuck at EarlierMeasureValue, I'm getting the err

or 'A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed', please can you help?

@vincentakatoh