I don't think your example data set is actually missing any data, but this should work: avg(zn([Sales]))
zn changes a null to zero
Alex, in my example, for 1/1/2012 data, there is one store didn't have any sales for product B, in that case, the store count is 4, instead of 5. We need total/5, not total/4. In the raw data, there is no 0, no NULL for product B for this store. The formula avg(zn([Sales])) returns same result.
Got it - no entry at all.
In this case we can count the total distinct stores. To do this we need to extract the data as count distinct is not available otherwise. Then we can find the max value across all of the partitions:
We can divide sales by this.
If you didn't want to extract, but were certain that at least one product or date had every store and that there were not multiple same store entries for that date or product, you could use count. Countd is much safer.
AverageCalculation.twbx.zip 13.5 KB