SPC chart (I-MR) - Line chart, straight line using Single Value

[vincentakatoh]

Hi, 

Objective is to create I-MR chart (simplest of SPC chart type). I'm almost complete with great help from forum. Final help needed.

Help needed: In Line Chart, need to add 2 straight lines (from measures). a) Upper Control Limit, UCL and b) Lower Control Limit, LCL. One Single value instead of computing across days. 

Problem: Due to calculations, the UCL and LCL lines are computed across days. My goal is 2 straight lines with UCL= 0.066 and LCL=0.015. Actual data set is much larger, as such i cant use the analytics function to draw 2 constant lines. 

LCL = [Mean]-2.66*[Sigma]

UCL = [Mean]+2.66*[Sigma]

sample pbix https://1drv.ms/u/s!ArjVwEnHONXNgh2-T7FtnCPcmkYg

Other DAX

UCL = [Mean]+2.66*[Sigma]

Sigma = [MRBar]/1.128

MRBar = AverageX(ALLSELECTED(data1[TimeSPC]);[MovingRange])

MovingRange = 
IF(ISBLANK(data1[EarlierTime]);blank();(
VAR EarlierTime = CALCULATE ( MAX ( data1[TimeSPC] ); FILTER ( ALLSELECTED ( data1[TimeSPC] ); data1[TimeSPC] < SELECTEDVALUE ( data1[TimeSPC] ) ) ) 
VAR EarlierMeasureValue = CALCULATE ( SUM ( data1[MeasureValue] ); data1[TimeSPC] = EarlierTime ) 
RETURN ABS ( EarlierMeasureValue - SUM ( data1[MeasureValue] ) ) ))

[Anonymous]

UCLConstant = CALCULATE(  [Mean]+2.66*[Sigma], ALLSELECTED(  data1[TimeSPC]  ))

[v-huizhn-msft]

Hi @vincentakatoh,

Actually, I am confused your sentence: "Actual data set is much larger, as such i cant use the analytics function to draw 2 constant lines." You can add two lines easily as follows.



Best Regards,
Angelia

[dirkjs]

Hi @vincentakatoh

When you calculate mean for the year, you will have 2 lines LCL and UCL on a constant value. The measure mean is as follows:

Mean =
CALCULATE (
AVERAGE ( [MeasureValue] );
FILTER ( ALL ( data1 ); data1[MeasureValue] )
)

[NApplewh1]

I have an Excel SPC chart but need to add in data for different data sets and create an interactive  Power BI chart is this possible?