MDX - SQL Server Analysis Services 2012 Cube Development - page 223

CY 2008 $4,118,716.85 -49.93%

CY 2009 -100.00%

CY 2010 N/A

Grand Total $16,057,851.72 N/A

Note

To apply the same calculation across all the levels within the
[Date].[Calendar] hierarchy, simply remove the outer IIF statement.

4. The next query finds the products that account for at least 10 percent of the
total reseller sales amount for the respective category within May of 2008. To
do so we first find the product's category using the ANCESTOR function and
find the total reseller sales amount for that category. The ANCESTOR function
accepts a dimension member as the first parameter and the level at which
we need to find the ancestor. Next we compare the reseller sales amount for
the current product with the total amount for the category, multiplied by 0.1:

WITH MEMBER measures.[sum for category]
AS
SUM(ANCESTOR(product.[product
categories].currentmember,
product.[product categories].[category])
, measures.[reseller sales amount])
MEMBER measures.[category] AS
ANCESTOR(product.[product
categories].currentmember,