I'm looking for a better way to calculate control limits for an SPC chart in PowerPivot.
I can do it with a small amount of data (~100 rows), however it is very inefficient and slow when doing it on a larger data set =(1000s of rows).
The formula to calculate the control limits is: Mean ± Mean moving range * 2.66 (where the MMR is the mean of the absolute difference between each value, in order)
The measures I am currently using to calculate this are:
The simplest way to deal with it would be to have a calculated column with the moving range, however I want to slice it using various columns so this isn't possible.
I have included some sample data below:
The way I am currently using it is in a pivot table with [ID] as rows and the 2 category columns as slicers. The values contain the measures [Current Value], [Mean], [Upper Limit] and [Lower Limit].
Any help is appreciated.
I can do it with a small amount of data (~100 rows), however it is very inefficient and slow when doing it on a larger data set =(1000s of rows).
The formula to calculate the control limits is: Mean ± Mean moving range * 2.66 (where the MMR is the mean of the absolute difference between each value, in order)
The measures I am currently using to calculate this are:
Excel Formula:
Current Value:=
LASTNONBLANK(SPCData[Value],1)
Previous Value :=
CALCULATE (
MAX ( SPCData[Value] ),
FILTER ( ALLSELECTED ( SPCData ), SPCData[ID] < MAX ( SPCData[ID] ) )
)
Moving Range:=
IF(ISBLANK([Previous Value]),blank(), ABS([Current Value] - [Previous Value]))
Mean Moving Range:=
AVERAGEX(ADDCOLUMNS(ALLSELECTED(SPCData),"mr",[Moving Range]),[mr])
Mean:=
CALCULATE(AVERAGE(SPCData[Value]),ALLSELECTED(SPCData[ID]))
Upper Limit:=
[Mean]+[X Moving Range]*2.66
Lower Limit:=
[Mean]+[X Moving Range]*2.66The simplest way to deal with it would be to have a calculated column with the moving range, however I want to slice it using various columns so this isn't possible.
I have included some sample data below:
| ID | Value | Category 1 | Category 2 |
1 | 80.71259132 | A | C |
2 | 70.16593087 | B | C |
3 | 12.3173281 | A | C |
4 | 11.17014753 | B | C |
5 | 88.41041659 | A | C |
6 | 62.00564824 | B | D |
7 | 58.2748246 | A | D |
8 | 80.37695074 | B | D |
9 | 49.26807429 | A | D |
The way I am currently using it is in a pivot table with [ID] as rows and the 2 category columns as slicers. The values contain the measures [Current Value], [Mean], [Upper Limit] and [Lower Limit].
Any help is appreciated.
