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!!!
What I would suggest doing is if you can just get a dataset for the months that you are testing, and by using the merge to see it can get to the number you are expecting to be the result?
This should then ensure that the theory is correct. Which is important to know that you can calculate the final number.
Then if the above works, if you had to bring in all the data, how large would the data model be? As there are always ways to optimize the data model. And I have worked with large datasets with a few million rows and it has still been very fast both when developing as well as once deployed to the Power BI Service.