Hi all

I've created a calculated member in measures, based on 2 measures, using the
following expression:

[Measures].[Amount] * [Measures].[ExchRate]

This gives me some strange sums, as the result of the above is multiplied bt
the number of records.

Can anyone tell me how to avoid this ?

What do you mean by "the number of records"?

If you post your query up here it makes it alot easier for people to assess
the issue.

Does the calculated member exist in the cube or is it calculated on the fly
in your query WITH block?

I could speculate as to the problem but...more info please!

If you could post a portion of your results up here then that would be
useful also. Perhaps the results of the following:
select {Measures.Amount, Measures.ExchRate,Measures.<your_calc_measures>} on
columns, <Time_dimension>.members on rows from <your_cube>


Regards
Jamie Thomson
http://www.conchango.com


"Thomas Christiansen" wrote:

Hi Thomas,

Can I confirm my guess that what is happening is that Amount and Exchange
Rate are being aggregated before being multiplied together? So that what
you're getting is (and I hope the layout doesn't get mangled here):

Your_Dimension Amount Exchange_Rate Calculated_Measure
1998 15 0.3 4.5
Jan 10 0.1 0.1
Feb 5 0.2 1.0

Instead of

Your_Dimension Amount Exchange_Rate Calculated_Measure
1998 15 n/a 1.1
Jan 10 0.1 0.1
Feb 5 0.2 1.0

If so, then you should do one of the following:
1) Perform the calculation in your fact table directly or in a view and
build it as a normal (ie not calculated) measure.
2) Create a new normal measure in your cube and in the Source Column
property, instead of simply putting the column name, put a SQL expression
which multiplies these two columns together. The end result of this is the
same as (1).
3) Change your calculated measure to find the set of all members of all
relevant dimensions at the leaf level, do the calculation for each of them,
and then sum up the result. Don't do this unless you have no other choice,
because it will slow down query performance a lot.

HTH,

Chris

"Thomas Christiansen" wrote: