Standard Deviation - Dynamic If statement

[Anonymous]

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!

[v-jingzhang]

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]

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