I have a model with probably about 10 fact tables. They are all at the same granularity
Product, Outlet, Fiscal Period
My issue is that demand units , inventory units, Product price, expiring stock units are all on seperate fact tables
I have created measures called
[demand units] , [inventory units], [Product price], [expiring stock units]
My measure for Overstock value is
if [demand units] = 0
then ([Invetory units] * [Product Price]) - ([expiring stock units] * [Product Price])
else ([Invetory units] * [Product Price]) - ([expiring stock units] * [Product Price]) - ([demand units] * [Product Price])
When displayed at the lowest level in the Product hierarchy this works as expected
However at any level above the base product level when it does the calculation totals , subtotals do not match because it is not the sum of the lower level.
Is there a way of forcing this to calculate at the lower level and then Sum that to the levels above.
The only way I have seen of this being done is by using SELECTCOLUMNS and then unioning all the fact tables .
Looking for a best practice for this sort of scenario