Calculating Relative Contribution

A tuple such as [Sales Units]—which uses an explicit member name from one dimension—are very common in MDX expressions. A tuple such as ( [Dairy], [Sales Units] )—which uses explicit member names from two or more dimensions—are quite rare. Usually, when you explicitly include members from more than one dimension in a tuple, you use functions to calculate a member based on the current member.

LookupCube("Sales","([Sales Dollars],[All Product],"
+[Time].[Calendar].CurrentMember.UniqueName + ")")

Calculate Contribution as a Percent of the Total

Suppose that you want to calculate what percent each product or product category contributes to the total sales for all products. This expression would have two component values: a numerator value (Sales for the current product) divided by a denominator value (Sales for All Products). The numerator value will change from product to product, but the denominator value always retrieves the member from the All level of the Product dimension. Start by creating a calculated member that displays the Sales for All Products.

1.	In the Cube Editor, change the Parent Dimension property of the Sample calculated member to Measures. Select the Value property, click the ellipsis button, and clear the current contents of the Value Expression box.
2.	Type ( [Sales Units], Ancestor( [Product].CurrentMember, [Product].[(All)] ) ) in the Value Expression box. The name of the All level in the Ancestor function includes parentheses. The brackets are then required because parentheses are special characters. The complete expression consists of a tuple that retrieves the value from the [Sales Units] member of the Measures dimension and from the All level member of the Product dimension (using the current member of all remaining dimensions). You could, of course, just enter [All Product] as the second part of the tuple, but then the expression would become invalid if you were to change the name of the Product dimension All level member. Note MDX has a DefaultMember function for a dimension. You should avoid using the DefaultMember function to refer to the All level member of a dimension because you can change the default member of a dimension, as described in “Specify a default member” in Chapter 3.
3.	Click the OK button to save the changes to the Sample member. [View full size image]
4.	Click the Value property ellipsis button, and change the contents of the Value Expression box to [Sales Units] / ( [Sales Units], Ancestor( [Product].CurrentMember, [Product].[(All)] ) ). This adds the numerator to the relative contribution expression. The numerator includes only the Measures dimension because it should “float” with the current member of the Product dimension. Since the numerator contains only a single member, parentheses are not required. This tuple returns the value of the Sales Units measure for the current product, the current state, the current time period, and so forth. The numerator tuple varies from product to product. The denominator tuple always returns the same product—the default member.
5.	Click OK to accept the revised definition. [View full size image]
6.	Drag the State dimension to the row area of the grid, replacing the Product dimension. [View full size image] The values for the Sample member all become 1.00. The numerator calculates the value of Sales Units for the current state and current product. The denominator calculates the value of Sales Units for the current state and default product. But each row of the grid shows the same product—the default product. When you create a calculated member, you must take into consideration any dimension that might appear in a grid browser. To make the Sample measure calculate the percent of total for both the State dimension and the Product dimension, you must add the State dimension to the denominator tuple.
7.	Select the Value property, and click the ellipsis button. Click between the final two closing parentheses, type a comma, and type the expresstion Ancestor([State].CurrentMember, [State].[(All)]). The final full expression should be [Sales Units] / ( [Sales Units], Ancestor( [Product].CurrentMember, [Product].[(All)] ), Ancestor([State].CurrentMember, [State].[(All)]) ). Click OK to accept the expression. [View full size image] The denominator tuple now includes members from three dimensions. The Sample member now shows the relative contribution of each member of the State dimension (while still retaining the ability to show the relative contribution of members of the Product dimension). If you want to expand the member to be able to show relative contribution for the Time.Fiscal, Time.Calendar, and Employee dimensions as well, you simply add an appropriate Ancestor function for each of those dimensions to the denominator tuple.
8.	Double-click the Country level to show the relative contribution of each region. [View full size image]

Calculate Contribution as a Percent of a Parent

Calculating the relative contribution to the total is a relatively easy calculation to create in a spreadsheet. A spreadsheet, however, has no conception of a hierarchy. In a spreadsheet, it’s extremely difficult to create a formula that will calculate the relative contribution of each region to its country or of each state to its region. One of the remarkable strengths of OLAP is the ability of expressions to understand the hierarchical relationships in dimensions.

1.	Select the Value property of the Sample calculated member, and click the ellipsis button. Change the Value Expression to [Sales Units] / ( [Sales Units], Ancestor( [State].CurrentMember, 1) ). In the revised expression, the denominator tuple uses the distance version of the Ancestor function to find the parent of the current product.
2.	Click OK to accept the revised definition. [View full size image]

The totals for Bread, Dairy, and Meat sum to 1 (100 percent). Likewise, the totals for the Bread subcategories sum to 1. The value for the All Product member, however, appears strange. This is a divide-by-zero error that the browser is attempting to format with two decimal places. The All Product member does not have a parent, so the value of for that parent is “empty,” which is treated as 0.

Check for an Empty Member

The expression to calculate the percent of parent must behave differently for the top member of a dimension than it does for all the other members. To do that, you use the IIF function described earlier in this chapter. As the conditional test, you can check whether the value of the denominator tuple is empty.

1.	Select the Value property of the Sample calculated member, and click the ellipsis button. In the Functions tree, expand the Logical folder and select the IsEmpty function. Look at the description of the function at the bottom of the dialog box. [View full size image]
2.	Select the current contents of the Value Expression box, and press Ctrl+C to copy it to the clipboard.
3.	Change the contents of the Value Expression box to IsEmpty( ( [Sales Units], Ancestor( [State].CurrentMember, 1 ) ) ). (Specifically, delete the numerator and the slash. Add IsEmpty at the beginning of the expression, and add a third set of parentheses after the tuple.) You need three sets of parentheses: the outer set of parentheses is required by the IsEmpty function. The inner set of parentheses is required by the tuple. The middle set of parentheses is required by the Ancestor function.
4.	Click OK to accept the definition. [View full size image]
5.	Click the Value property ellipsis button to redisplay the Calculated Member Builder. Change the contents of the Value Expression box to IIF(IsEmpty( ( [Sales Units], Ancestor( [State].CurrentMember, 1 ) ) ), 1, [Sales Units] / ( [Sales Units], Ancestor( [State]. CurrentMember, 1 ) ) ). (Specifically, enter IIF and an opening parenthesis at the beginning of the expression. Type a comma, a 1, another comma, and then press Ctrl+P to paste the previous expression. Finally, type a closing parenthesis at the end of the expression.)
6.	Click OK to accept the definition. [View full size image] Note Rather than test for an empty value, you may prefer to test for when the current member is at the All level of the hierarchy. The expression [Product]. CurrentMember.Level.Name returns the level name of the current product. For the All level, it will be equal to “(All)”. Alternatively, the expression [Product]. CurrentMember.Level.Ordinal returns the level number of the current product. For the All level, it will be equal to 0.