Nelson Rule Implementation in Power BI

[sh_Himanshu]

I have to create a control chart which includes Mean , Std dev & control limits as well along with that we need to implement Nelson Rules in it which shows different violated points in diff colors.
if we talk about that chart we would like to see Batch IDs on the x axis and sum of Numer_Values on the Y axis. Batch IDs are string type and contains 'A01234' or 'BSD123' or '123456' this type of entries. plotting of points would be on Batch Mfg Dates which are not continous, we can see differences in between points.
First visual would be sorted on mfg date and if dates are same then on batch numbers.
Can somebody please help me with the DAX code for all the rules by creating and implementing the Nelson Rules.

I am new to this type of visual so please thanks in advance.

[bhanu_gautam]

@sh_Himanshu , Try using 

Mean_Numer_Values = AVERAGE('Table'[Numer_Values])

StdDev_Numer_Values = STDEV.P('Table'[Numer_Values])

UCL = [Mean_Numer_Values] + 3 * [StdDev_Numer_Values]
LCL = [Mean_Numer_Values] - 3 * [StdDev_Numer_Values]

One point more than 3 standard deviations from the mean):

Nelson_Rule1 =
IF(
ABS('Table'[Numer_Values] - [Mean_Numer_Values]) > 3 * [StdDev_Numer_Values],
1,
0
)

Nelson Rule 2 (Nine (or more) points in a row on the same side of the mean):

DAX
Nelson_Rule2 =
VAR CurrentIndex = RANKX(ALL('Table'), 'Table'[Mfg_Date], , ASC, DENSE)
VAR PreviousValues =
CALCULATETABLE(
TOPN(8,
FILTER(
ALL('Table'),
RANKX(ALL('Table'), 'Table'[Mfg_Date], , ASC, DENSE) < CurrentIndex
),
'Table'[Mfg_Date],
ASC
)
)
VAR SameSideCount =
COUNTROWS(
FILTER(
PreviousValues,
SIGN('Table'[Numer_Values] - [Mean_Numer_Values]) = SIGN(EARLIER('Table'[Numer_Values] - [Mean_Numer_Values]))
)
)
RETURN
IF(SameSideCount >= 8, 1, 0)

Add a scatter plot visual to your report.
Set the X-axis to Batch IDs and the Y-axis to the sum of Numer_Values.
Add the measures for Mean, UCL, and LCL as reference lines.

Use conditional formatting to color code the points based on the Nelson Rules measures.
For example, if Nelson_Rule1 is violated, color the point red.

[sh_Himanshu]

@bhanu_gautam Thanks for guiding.

Here couple of doubt for rule2 , If mfg dates are same for more than 1 rows then ranking is going to be same so to how to overcome that and also getting some error. with 

SameSideCount

Attached is the screenshot, please help me here

thanks

[Anonymous]

Hi @sh_Himanshu ,

Based on the description, try to add a secondary ranking based on Batch ID to make sure unique ranking. Use the following DAX formula.

Nelson_Rule2 = 
VAR CurrentIndex = RANKX(ALL('Table'), 'Table'[Mfg_Date] & 'Table'[Batch_ID], , ASC, DENSE)
VAR PreviousValues = CALCULATETABLE(
    TOPN(8, FILTER(ALL('Table'), RANKX(ALL('Table'), 'Table'[Mfg_Date] & 'Table'[Batch_ID], , ASC, DENSE) < CurrentIndex), 'Table'[Mfg_Date], ASC)
)
VAR SameSideCount = COUNTROWS(
    FILTER(PreviousValues, SIGN('Table'[Numer_Values] - [Mean_Numer_Values]) = SIGN(EARLIER('Table'[Numer_Values] - [Mean_Numer_Values])))
)
RETURN IF(SameSideCount >= 8, 1, 0)

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.