Forum Discussion
Including 0 values when calculating STDEV
GilbertQ Thank you for the help; that would be a great work around except that I m dealing with more than 1000 SKUs so my query would produce a few million rows given we transact on a daily basis and are reporting on 3 to 5 year period.
I thought about manaully calculating standard deviation instead of using the DAX STDEVX.P formula and found these 2 as a reference on how to do so, the first being closest to my need:
https://community.powerbi.com/t5/Desktop/Problem-with-STDEV/td-p/19731
So, given my detail above, I attempted to make a go of it, but still am not getting the correct number. My code is:
STDEVX2 =
var Averageprice=[6M Sales]
var months=6
return
SQRT(
DIVIDE(SUMX(
FILTER(ALL(DimDate),
DimDate[Month ID]<=(MAX(DimDate[Month ID])-1) &&
DimDate[Month ID]>=(MAX(DimDate[Month ID])-6)
),
(iContractsChargebacks[SumOfOrderQuantity]-Averageprice)^2),
months
)
)
I am determined to figure this out and I appreciate all your help with this. Thank you in advance!!!
Hi,
did you was able to find a solution? I'm facing with a similar probelm (I would calculate standard deviation on latest 52 weeks but I have some week with no data)...