topic Re: Process Improvement Measures (Cp, Cplower, Cpupper, Cpk, Cpm, Cpkm) in Quick Measures Gallery

Process Improvement Measures (Cp, Cplower, Cpupper, Cpk, Cpm, Cpkm)

Greg_Deckler — Wed, 27 Jun 2018 20:43:51 GMT

Given some sample data:

MeasureValueLimitUpperLimitLowerIndexTarget

0.05	0.05	0	0.03
0.04	0.05	1	0.03
0.04	0.05	2	0.03
0.05	0.05	3	0.03
0.04	0.05	4	0.03
0.04	0.05	5	0.03
0.05	0.05	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

Re: Process Improvement Measures (Cp, Cplower, Cpupper, Cpk, Cpm, Cpkm)

vincentakatoh — Thu, 22 Feb 2018 04:54:08 GMT

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.

Re: Process Improvement Measures (Cp, Cplower, Cpupper, Cpk, Cpm, Cpkm)

vincentakatoh — Thu, 22 Feb 2018 05:16:48 GMT

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

Re: Process Improvement Measures (Cp, Cplower, Cpupper, Cpk, Cpm, Cpkm)

vincentakatoh — Thu, 22 Feb 2018 13:04:26 GMT

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

Re: Process Improvement Measures (Cp, Cplower, Cpupper, Cpk, Cpm, Cpkm)

Greg_Deckler — Thu, 22 Feb 2018 13:54:13 GMT

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!

Re: Process Improvement Measures (Cp, Cplower, Cpupper, Cpk, Cpm, Cpkm)

vincentakatoh — Thu, 08 Mar 2018 16:18:32 GMT

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)

Re: Process Improvement Measures (Cp, Cplower, Cpupper, Cpk, Cpm, Cpkm)

igor722 — Tue, 05 Jun 2018 07:23:55 GMT

Hello. I am very new to this.

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

Greetings

Re: Process Improvement Measures (Cp, Cplower, Cpupper, Cpk, Cpm, Cpkm)

Anonymous — Wed, 15 May 2019 11:46:55 GMT

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