cancel
Showing results for 
Search instead for 
Did you mean: 

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

Super User
2419 Views
Super User
Super User

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

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)

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


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

@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.

 

2018-02-22 12_50_05-Moving Range - Excel.jpg

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

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)

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

 

2018-02-22 21_02_57-ImR Chart3 - Power BI Desktop.jpg

 

 

Super User
Super User

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

@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!


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


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

@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)
igor722 Frequent Visitor
Frequent Visitor

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

Hello. I am very new to this.

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

Greetings