MSAS 2000 and a Virture Cube
Dimensions:
Time_Dim is Year -> Month
Prod_Dim is Brand -> Prod -> Cat -> SubCat -> SKU
Code_Dim is Code -> SubCode
Measures: A and B

I have a calculated member that is CA = MA * MB

I only need that CA at month level, so time dim is not a problem.

However, the calculation is wrong if I am at a higher level, i.e. SubCat
than SKU in the Prod_dim. It seems that the default way for CA is MA of (SKU1
+ SKU2 + SKU3....) * MB of (SKU1 + SKU2 + SKU3...). What I wanted is (MA *
MB) of SKU1 + (MA * MB) of SKU2 + (MA * MB) of SKU3....

Do I write a Custom Rollup Formula in the member Porperties of each
dimension? If that's the case, could you give me a formula example for SubCat
level of Prod_DIM?

Are measures A & B part of the same physical cube?

If so, the best thing to do is to create a "real" measure with the
expression MA * MB. Otherwise it gets a bit harder because you are
essentially fighting against the natural aggregations.

If the issue is just around the product dimension - I think you could
create a calculated measure something like the following:

SUM(DESCENDANTS(Prod_Dim.CurrentMember,Prod_Dim.SK U), MA * MB)

However, If you need to do the multiplication at the leaf level of more
than the product dimension then it gets a bit more complex.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <3F8EED39-4CAD-4620-B24F-956741F7D2A5 (AT) microsoft (DOT) com>,
MJ (AT) discussions (DOT) microsoft.com says...

Thanks Darren.
I would not be able to create a "real" measure in this case. MA and MB are
from different cubes. I have 4 dimensions and 3 measures in the virture cube.

I have a IIF statement checking for time_dim. If I am at the month level, do
MA*MB, else NULL. If I do that kind of checking for all 4 dimensions, the
nested IIFs will go forever.

IIF time at month
IIF prod at sku
IIF code at subcode
IIF dim_4 at the leaf
.....

What other solution do I have? Please.


"Darren Gosbell" wrote:

I did not think the odds were too good that it would have been as simple
as creating a "real" measure, but it is always best to double check
these things

Actually, I just remembered that Mosha has an article on the situation
you are facing here:
http://www.mosha.com/msolap/articles...ggregation.htm

The article is worth reading in full, but in summary you would end up
with a calculation something like the following:


WITH MEMBER [Measures].[AB] AS
'Sum (
Descendants ([Time].CurrentMember, [Time].[Month]) *
Descendants ([Code_Dim].CurrentMember, [Code_Dim].[subcode]) *
Descendants ([Prod_Dim].CurrentMember, [Prod_Dim].[SKU]),
([Measures].[A] * [Measures].[b])
)'


--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <7671042C-64A5-4AE1-98F3-45768118305A (AT) microsoft (DOT) com>,
MJ (AT) discussions (DOT) microsoft.com says...