Average of all Standard Deviations cannot summarise correctly

[Anonymous]

Hello everyone,

hopefully someone could help on the below.

I am trying to calculate the Standard Deviation(SD) of all the Sales at SKU level across all the dates we are looking at and then get the Average of all these Standard Deviations.

The measure created calculates the right thing at row level (so for store 5 item 1001027 indeed SD is 2.47 but then when you take the total shown on bottom what PowerBi does is it calculates SD across all Sales recorded for this line in all the stores.  Should be 0.92 but calculates 3.68 instead( please see images below).

My SD formula is the below and we are unsure how to get it to take the average of the results; we tried adding CALCULATE(AVERAGE) but does not work and think we cannot do at column level as SD needs to look across all dates.

SD = STDEVX.P(SUMMARIZE('scdm RPT_FORECAST_STOCK_SALES_ITEM_STORE_WLY','scdm RPT_FORECAST_STOCK_SALES_ITEM_STORE_WLY'[ITEM_CODE],'scdm RPT_FORECAST_STOCK_SALES_ITEM_STORE_WLY'[STORE_NUMBER],'scdm RPT_FORECAST_STOCK_SALES_ITEM_STORE_WLY'[WEEK_ENDING_DATE]),CALCULATE(SUM('scdm RPT_FORECAST_STOCK_SALES_ITEM_STORE_WLY'[POTENTIALSALES_EXCL_TAF])))

Any ideas to get the correct total as an average of all the Item-Store level across all dates SD would be very helpful.

Many thanks in advance.

Result I get(incorrect total)Actual Avg SD of all SD if calculated in Excel

[lbendlin]

Get rid of the last Calculate and see if that helps any. If not, post your sample data in a usable format.

[Anonymous]

Thanks you I tried it but gives SD of 0 when I remove calculate.

Please note I  am using direct query instead of import but all my data for the SD calculations are within 1 table.

Not sure how to attach the file in the forum. i have created a dropbox link

https://www.dropbox.com/scl/fi/3rf1soj507sptmwdw5e3s/1001027-SD-Example.xlsx?dl=0&rlkey=3h44owsiyjsh...

[lbendlin]

In your Excel file you are just averaging column M, regardless of the store sums per week. Isn't that skewing the result?  What are you actually trying to measure?

[Anonymous]

Yes it would skew the result but comparing that witha Average sales across stpres is slightly better than taking teh total sales and total average irrespective of store average. Ultimately I would need to calculate the safety stock at Item-Store level to allow me to get to 2SD away from that store's average sales. But I need it to also aggregate correctly at a total level.

Many thanks

Dimitra