Forum Discussion
Including 0 values when calculating STDEV
Hello Everyone,
I am attempting to calculate the 26 Week STDEV for weekly sales. The Sales records are daily so I am using a Date table to aggregate them by week.
The problem I am coming into is when one of the weeks total sales for a specific product is 0, then the STDEV formula below ignored that period and the 0 for that period, which skews the standard deviation.
How can I get the STDEV to conisder the periods where sales are zero?
Thanks you!!
26-W.STDEV = CALCULATE(
STDEVX.P(
VALUES(DimDate[Calendar Week Number]),
CALCULATE(SUM(iContractsChargebacks[ChargebackUnits]))
),
FILTER(ALL(DimDate),
DimDate[Week ID]<=MAX(DimDate[Week ID])-1 &&
DimDate[Week ID]>=MAX(DimDate[Week ID])-26
))
10 Replies
GilbertQ Sorry for the delay. There is actually no value. Let me walk you through a better example. I am attempting to determine the most recent 6-Month STDEV for sales by product in order to determine variation.
The Sales Data is made up of daily transactions, so it contains a iContractsChargebacks[TransactionDate] and iContractsChargebacks[ChargebackUnits] (i.e. sales), but if there are no sales in a given month, then there will be no data for that month.
So, for example, on July 1st, sales for the past 6 months were the following:
Jan 100
Feb 125
Apr 140
May 125Jun 130
March is missing because there were no sales. So, when I calculate STDEV on the data set, it is calculating it over 5 periods, when in fact there were 6, just one happend to be zero.
The code I am curretly using is:
6M STDEV = CALCULATE(
STDEVX.P(
VALUES(DimDate[Calendar Month Number]),
CALCULATE(SUM(iContractsChargebacks[ChargebackUnits]))
),
FILTER(ALL(DimDate),
DimDate[Month ID]<=MAX(DimDate[Month ID])-1 &&
DimDate[Month ID]>=MAX(DimDate[Month ID])-6
))Instead of using date parameters in the code, I created a calculated column in the date table that gives each Month a unique ID, makes it easier for me.
What I need the formula to do is calculate STDEV across the six month period. If when pulling the monthly sales numbers, it only comes back with 3 periods, then it needs to assume the other 2 periods with a zero value.
Thank you for your help with this!!
Hi epicleo,
As the GilbertQ said, if all they are null where sales are zero, you can change it by calculating a calculated column using the formula.New Column=IF(ISBALNK(Table[column]),0,Table[column])
Then use the new column in your 26-W.STDEV formula, and check if it works fine.
Best Regards,
AngeliaSorry for the delay. There is actually no value. Let me walk you through a better example. I am attempting to determine the most recent 6-Month STDEV for sales by product in order to determine variation.
The Sales Datais made up of daily transactions so it contains a iContractsChargebacks[TransactionDate] and iContractsChargebacks[ChargebackUnits] (i.e. sales), but if there are no sales in a given month, then there will be no data for that month.
So, for example, on July 1st, sales for the past 6 months were the following:
Jan 100
Feb 125
Apr 140
May 125Jun 130
March is missing because there were no sales. So, when I calculate STDEV on the data set, it is calculating it over 5 periods, when in fact there were 6, just one happend to be zero.
The code I am curretly using is:
6M STDEV = CALCULATE(
STDEVX.P(
VALUES(DimDate[Calendar Month Number]),
CALCULATE(SUM(iContractsChargebacks[ChargebackUnits]))
),
FILTER(ALL(DimDate),
DimDate[Month ID]<=MAX(DimDate[Month ID])-1 &&
DimDate[Month ID]>=MAX(DimDate[Month ID])-6
))Instead of using date parameters in the code, I created a calculated column in the date table that gives each Month a unique ID, makes it easier for me.
What I need the formula to do is calculate STDEV across the six month period. If when pulling the monthly sales numbers, it only comes back with 3 periods, then it needs to assume the other 2 periods with a zero value.
Thank you for your help with this!!
Hi epicleo
What I would suggest doing is rather than to try and figure out when there is no data I would solve it by doing the following.I would left join from your DimDate table, to your Fact (Source Data) table. By doing a left join from the DimDate table it will then bring in all the dates. This will then allow your data to be blank when there is no data.
Then based on your requirements you can then use the same measures and not it should show zero for the months where there is no data when you put in your Date column from your DimDate table.
