Forum Discussion
Nelson Rule Implementation in Power BI
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.
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
Attached is the screenshot, please help me here
thanks