cancel
Showing results for 
Search instead for 
Did you mean: 

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

Super User
2459 Views
Highlighted
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