The Rollup of Sum of product of two dimension members not correct

[vasun]

Dear Experts I have a situation here ...

I have a Lineitem Dimension which has two members. For example
1.Sales
2.Standard Cost/Unit

Now i have created a new calculated member attached to the Lineitem Dimension called
3.Budget Sales = Sales * Standard Cost


Here is the Source Data
Time Sales Standard Cost/Unit
Aug 2003 200 5
Sep 2003 300 2


This is What i get as a result when the values get rolled up across time dimension

Time Budget Sales
->2003
->Q3 3500 (Wrong)(Should be 700)
->Aug 100 (200 * 5)
->Sep 600 (300 * 2)

The problem is 3500 $ which is (200+300) * (5+7) . By default the cube rollup aggregation is
set to SUM which sums up the Lineitems Sales,Standard Cost/Unit before it multiplies at Q3 level.


Is there any solution to get around this issue. I have tried custom rollup etc but that affects
only one dimension.The aggegation should come clean across all the dimension. Please help.

 

[MDXer]

look into Calculated Cells. The SQL BOL documentation on them isn't the greatest and they can be a bit difficult to get used to.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck