cancel
Showing results for
Did you mean:

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

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

Proud to be a Datanaut!

Member

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

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.

Member

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

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

Member

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

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

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!

Proud to be a Datanaut!

Member

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

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)```
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