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

Luckily, one simple correction to the formula solves both problems, leading
to a much clearer and optimized expression. We define a hidden member,
SalesInCountry , that returns the sales in the current country and then substitute
it for the expression returning sales at the Country level in the original calculation:
CREATE MEMBER CURRENTCUBE.Measures.SalesInCountry AS
(
[Measures].[Sales Amount],
Ancestor (
[Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].Country
)
),
VISIBLE = 0;
CREATE MEMBER CURRENTCUBE.Measures.RatioOverCountry AS
IIF (
Measures.SalesInCountry = 0,
NULL,
([Measures].[Sales Amount]) / Measures.SalesInCountry
),
FORMAT_STRING = "Percent";
The new intermediate measure has the property VISIBLE set to 0 , which means that
the user will not see it. Nevertheless, we can use it to make the next formula clearer
and to let the formula engine cache its results in order to speed up computation.
With SSAS 2012 SP1, you can easily rewrite the previous code in a more elegant way
by using the new DIVIDE function. DIVIDE makes it easier to write divisions without
having to worry about the initial IIF to check the denominator for zero or NULL :
CREATE MEMBER CURRENTCUBE.Measures.RatioOverCountry AS
DIVIDE (
[Measures].[Sales Amount],
Measures.SalesInCountry,
NULL
),
FORMAT_STRING = "Percent";
The DIVIDE function accepts three parameters: numerator, denominator, and default
value. In case the denominator evaluates to zero or NULL , DIVIDE returns the default
value instead of raising an error.
It is worth to note that DIVIDE is useful to avoid the test for the division by zero, but
it does not solve the issue of caching the intermediate result. The definition of the
SalesInCountry measure is still useful, even if DIVIDE is used.