Database Reference
In-Depth Information
The formula is correct in this case, but in order to make it work properly for any
measure, we should use the following:
CREATE MEMBER CURRENTCUBE.Measures.[Sales Amount YTD] AS
AGGREGATE (
YTD ([Date Order].[Calendar].CurrentMember),
Measures.[Sales Amount]);
Here we replaced the MDX SUM function with AGGREGATE . The result for Sales
Amount is the same. However, if we need to use this formula on a measure that does
not have its AggegationFunction property set to Sum , AGGREGATE will aggregate
values differently for each different AggegationFunction , whereas SUM will always
provide the sum. For example, consider a measure that has its AggegationFunction
property set to DistinctCount : if we had 5 distinct customers on 1 st January, 3 distinct
customers on 2 nd January, and 4 distinct customers on 3 rd January, the total number of
distinct customers from the 1 st to the 3 rd of January would not be 5+3+4=12 because
some customers might have sales on more than one day. Using AGGREGATE in this case
will provide the correct number of distinct customers in the year-to-date.
Ratios over a hierarchy
Another very common pattern of calculated member is the ratio. A good example of
this is when we want to compute the ratio of a measure (for example, sales amount)
on a member of a hierarchy against its parent.
Consider, for example, the Customer Geography hierarchy in the Customer
dimension. We might want to compute two different kinds of ratios:
Ratio over Country : This is the ratio of the selected member against the
whole of the country. This gives a percentage that shows how much a State
or City's sales contribute to the sales of the whole country.
Ratio over Parent : This is the ratio of the selected member against its parent
in the hierarchy rather than the whole country. So, for example, we would
see the percentage contribution of a City to the Region it was in, if City was
the level immediately underneath Region in the hierarchy.
 
Search WWH ::




Custom Search