Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Galleries
- Quick Measures Gallery
- Re: Process Improvement Measures (Cp, Cplower, Cpu...

02-20-2018 08:19 AM - last edited 06-27-2018 13:43 PM

Greg_Deckler

Super User

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-20-2018
08:19 AM

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

Proud to be a Datanaut!

vincentakatoh

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-21-2018
08:54 PM

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

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-21-2018
09:16 PM

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

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-22-2018
05:04 AM

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

Greg_Deckler

Super User

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-22-2018
05:54 AM

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

vincentakatoh

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-08-2018
08:18 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-05-2018
12:23 AM

Hello. I am very new to this.

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

Greetings

Highlighted
##

kirstique

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-15-2019
04:45 AM

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?