Forum Discussion
Including 0 values when calculating STDEV
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.
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!!!