Adding Calculations to the Cube - Expert Cube Development with SSAS Multidimensional Models - page 148

As the numerator, we put the sales amount; in the denominator we override
the Geography hierarchy with the ancestor of the CurrentMember at the level of
Country . So, if this measure is evaluated for ZIP code 75002 of Paris, the following
will happen: [Customer].[Customer Geography].CurrentMember evaluates to
75002, and its ancestor, at Country level, evaluates to France. In the numerator, the
Customer Geography hierarchy evaluates to 75002, while at the denominator, we
provide an explicit substitution and the same hierarchy evaluates to Paris. The ratio
between those two values for the Sales Amount measure is the percentage required.
We use the format string Percent that transforms decimal numbers into percentages
(0.1234 is 12.34 percent, 1 is 100 percent, and 2.5 is 250 percent).
The second version of the ratio is similar, and simply changes the denominator from
the fixed ancestor at the Country level to a relative one. The denominator is relative
to the CurrentMember : it is its parent. The formula is:
CREATE MEMBER CURRENTCUBE.Measures.RatioOverParent AS
(Measures.[Sales Amount])
/
(
Measures.[Sales Amount],
Customer.[Customer Geography].CurrentMember.Parent
),
FORMAT_STRING = "Percent";
The numerator is the same measure, but the denominator changes each time the
CurrentMember changes and is expressed as the parent of the CurrentMember . The
Parent function returns the immediate ancestor of a member in its hierarchy. So,
when the CurrentMember evaluates to ZIP code 75002 its parent will be Paris. When
the CurrentMember is Paris its parent will be Seine (Paris) and so on.
If we query the cube with these two formulas, we will get this result: