For example, the region dimension has the hierarchy:
Region 1: Northeast -> Division 1 and Division 2
Region 2: Midwest (Division 3 and Division 4)
Region 3: South (Division 5, Division 6, Division 7)
Region 4: West (Division 8 and Division 9)
But the fact table only has data for each individual division not region. Therefore, if I need to calculate the sales total for region level, I may need to use calculated measure member.
I wrote MDX to create the calculated measure. When I query the cube by using region as the only slicer (filter), the result for all regions can be displayed properly. But if I add additional slicer (e.g. year), the data is only displayed for some specific year (e.g. 2010 only) for all regions instead of all years (2011-2013). I cannot figure out what's going wrong and how to fix it.
Thanks again for your help!
You do not need to use calculated measures for aggregations like this. Analysis Services will do that for you. It is standard to load the fact data on bottom level of the dimensions, and let Analysis Services do the aggregations for you. That is what the "AggregateFunction" property for each measure is meant for.