Last month, we showed you one way to determine what percentage contribution individual pieces of a group have made. With the Sales cube in the FoodMart database sample, use the query in Listing A to analyze [Store Sales] as a percent of a group in the Product dimension, as Screen A shows.
Unfortunately, the member definition doesn't work very well if you navigate to the top of the product hierarchy such as [Product].[All Products]. Navigating to the top of the product hierarchy creates a division by zero error because the [All Products] level doesn't have a parent level.
To improve the percent of group measure defined in Listing A so that it returns 100 percent when the current member of the product dimension is the top member, try the query in Listing B. This query introduces a new MDX function, IIF, to our calculated member expression. The IIF function, an IF clause, will be familiar to many programmers because most languages support a version of it. In this case, you're checking whether the Product level is at the [All Products] level by checking the ordinal property. You're at the products root level, or the [All Products] level, if Product.CurrentMember.Level.Ordinal = 0. See Screen B.
(Remember, you can't run MDX expressions from an ISQL/w or Query Analyzer window. Many third-party front ends let you pass MDX to an OLAP Services box. If you don't have a third-party tool, use the MDX Sample Application installed by default in OLAP Services. We generated the screen shots we're using in this column with the MDX Sample Application.)