Forum Discussion

sh_Himanshu's avatar
sh_Himanshu
Regular Visitor
1 year ago

Nelson Rule Implementation in Power BI

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.

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      sh_Himanshu
      Regular Visitor
      1 year ago

      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's avatar
    Anonymous
    Not applicable
    1 year ago

    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.