This is the next recipe in our series of relative calculations. In this recipe, we're going to show how to calculate the average among siblings, the average in the complete hierarchy and the average on leaves. A good introduction is given in the previous recipe, the one about calculating various percentages. It is recommended that you read this series of recipes exactly as laid out in the book.
Start SQL Server Management Studio and connect to your SSAS 2008 R2 instance. Click on the New Query button and check that the target database is Adventure Works DW 2008R2.
Here's the query we'll start from:
WITH MEMBER [Measures].[Level] AS [Sales Territory].[Sales Territory] .CurrentMember.Level.Ordinal SELECT ...