Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Standard Deviation - Dynamic If statement

Hi All,

 

Need some help- I have managed to calculate the Average & Standard deviation of a location based on a dynamic Date filter using the following code:

 

 

Average_SD = CALCULATE((
    AVERAGEX(SUMMARIZE(Master_Incident_Data,'Alex Date Period Flow'[Period],"Incidents Per Period",
    CALCULATE([Count of incidents])),
    [Incidents Per Period]
    )),ALLSELECTED(Master_Incident_Data))

Standard_Deviation_SD = 
CALCULATE((
    STDEVX.P(
    SUMMARIZE(Master_Incident_Data,'Alex Date Period Flow'[Period],"Count Of Incidents",
    CALCULATE([Count of incidents])),[Count Of Incidents])),
    ALLSELECTED(Master_Incident_Data))

 

 

Then Calculated the various Upper & Lower Control Bounds eg:

 

 

1X_Above_SD = CALCULATE('Alex Date Period Flow'[Average_SD] + [Standard_Deviation_SD],ALLSELECTED(Master_Incident_Data))

 

 

Leaving me with:

 

My Question: How do I create an IF formula that will tell me if a value if below/ above a value for 3 periods in a row.

 

For example: Within this example in P11, P12, P13 this location would be below 1x Standard deviation for 3 periods in a row?

 

Any guidance would be fantastic. Thank yoU!

 

 

 

Need Help

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Anonymous 

     

    Sorry I don't understand the expected result. Do you want to compare [Count of incidents] with [1x_Lower_SD]? Or with [Standard_Deviation_SD]? How to understand the "3 periods in a row"? For example, for P11, which "3 periods" do they refer to?

     

    Best Regards,
    Community Support Team _ Jing

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-jingzhang , Thank you for responding.

       

      So I would like to compare [Count of Incidents] to [1x_Lower_SD]. 

       

      In excel the formula would be:

       

      =IF(AND P11< [1x_Lower_SD],P12<[1x_Lower_SD],P13<[1x_Lower_SD]), "-1 STD 3 Periods in a row", "N/A")

       

      If theres a way to make it dynmic to automtically highlight how many it has been in a row, so in the example it would be 4 periods in a row (P10-P13), that would be great but theres over 1,300 locations so may be abit intensive, but standard practice is 3 in a row.

       

      Regards,


      Alex