Calculated Member

[JTH]

Hello,

I have following problem:

A measure in a cube need to be divided by another measure as follows:

MEASURE1\MEASURE2

Measure 1 (SUM of Money spent by each person)
Measure 2 (Amount of Money available for each country per person).
Example:
USA: 155
Germany:134
France:143)

Measure1 is a SUM and works fine, but Measure2 should only be a distinct value for each country. So if person comes from Germany, then the SUM of Spent Money should be divided by 134.

Any idea how this can be done.

[Island1]

Does the divisor (the "denominator") follow a business rule that can be stored in a table, or derived from something stored in a table? Is this something similar to an exchange rate scenario?

Depending upon how static the divisor is, and whether you need to keep track of it historically for trending, restatement, GAAP or other purposes, a table containing the data - like an exchange rate - with the dates / date ranges it is in effect might make sense.

Other options include derivation based upon logic, but that might introduce considerable drawbacks; management in the reporting layer might be an option, if parameterization is a consideration and performance not a disqualifier. (But keep the possible need for historical reporting in mind before venturing upon a reporting layer option).

Even if it's not an exchange-rate-esque scenario, you can learn a great deal by studying these, and extrapolating the concepts to your own situation.

Good Luck.

Bill