Forum Discussion
Including 0 values when calculating STDEV
Hi epicleo
Are the values zero or are they NULL?
By default Power BI will not use any values that are NULL, but if they have any other value (including 0) it should then still consider it?
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 125
Jun 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!!