Hi,

This issue refers to the SQL 2005 June CTP:
We have a cube with inventory measures. In aggregations we want to see the
average, thus the AverageOfChildreen Aggregate Function suits our needs.
Because of storrage optimation in our datawarehouse we have one issue, that
posses a problem.
- We only store the inventory for a certain product if it is not empty (!=
0) otherwise we just don't store the data.
(changing this would increase the size of the datawarehouse more than 10
times)

e.g. Table in the DWH

Week, Day, Inventory
1 1 3
1 2 2
1 3 4
1 4 3
1 5 3
1 6 4
1 7 2
2 1 4
2 2 NULL (Not in Database)
2 3 2
2 4 4
2 5 2
2 6 NULL (Not in Database)
2 7 3
....

Aggregate Week 1 with AverageOfChildreen is fine (3+2+4+3+3+4+2) / 7 = 3
Aggregate Week 2 with AverageOfChildreen = (4+2+4+2+3) / 5 = 3
(which is obviously correct)
However the result we desired would have been: (4+0+2+4+2+0+3)/7 = 2.14

In order to archieve this I created a calculation [InventoryNotEmpty]:
[InventoryNotEmpty] =
IIF(ISEMPTY([Measures].[Inventory]),0,[Measures].[Inventory])

The funny and frustrating thing now is, if I put both Measures [Inventory]
and [InventoryNotEmpty] in a cube (using the BIDev OWC Browser) the numbers
shown for [InventoryNotEmpty] sometimes aggregated the way I wanted them to,
sometimes they didn't (They didn't eben show 0 for empty cells in
[InventoryNotEmpty]).

Maybe I am totally on the wrong track here, any help would be appreciated.

Björn