Forum Discussion

MarkShu's avatar
MarkShu
Helper I
7 years ago

Need help with standart deviation

Hello!  I have ad campaigns, budget and weeks.  How can I check standard deviation of budget on week from previous weeks (5 for example)? 
Need Help
MarkShu's avatar
MarkShu
Helper I
7 years ago

I want to compare standard deviation of budget ad campaign to in rubles week to week. For example in last week more then 2 standard deviations from ordinary values, then I will understand in this week I have problems. If standard deviation less then 2, then all are in normal range. But if I just use STDEV.P/STDEV.S function I have standard deviation within one week without comparing with previous weeks. 

  • MarkShu's avatar
    MarkShu
    Helper I
    7 years ago

    Hi v-cherch-msft!
    I done something already, but I have issue. 
    I have this:

    start date = IF(NOT ISBLANK (sum('А'[Budget));CALCULATE(FIRSTDATE('Date'[Date]);ALLSELECTED('Date')))

    finish date = CALCULATE(LASTDATE('Date'[Date]))

    n = IF(SUM('А'[Budget])>0;

    CALCULATE(SUMX(SUMMARIZE('А';'Date'[Week];"S";SUM('А'[Budget]));1);DATESBETWEEN('Date'[Date];[start date];[finish date])))



    x(i) = IF(SUM('А'[Budget])>0;CALCULATE(SUM('А'[Budget]);DATESBETWEEN('Date'[Date];DATEADD(FIRSTDATE('Dat'[Date]);-7;DAY);
    DATEADD(LASTDATE('Date'[Date]);-7;DAY))))

    |x| = IF(SUM('А'[Budget])>0;DIVIDE(CALCULATE(SUM('А'[Budget]);DATESBETWEEN('Date'[Date];[start date];DATEADD(LASTDATE('Date'[Date]);-7;DAY)));[n]-1))

    But then I try to calculate E (x(i)-|x|)^2 I recieve nothing, even error does not show. 

    E (x(i)-|x|)^2 = IF(SUM('А'[Budget])>0;SUMX(SUMMARIZE('А';'Date'[Week];"D";POWER([x(i)]-[|x|];2));[D]))