Calculating various averages

This is the second recipe in our series of relative calculations. In the previous recipe, Calculating various percentages, we created calculated measures for the Parent %, the Level %, the Hierarchy % and the Leaves %. We used the ragged Sales Territory hierarchy of the Sales Territory dimension. There is a good introduction to ragged hierarchy in the previous recipe.

In this recipe, we will continue to use the ragged Sales Territory hierarchy of the Sales Territory dimension. We are going to show how to calculate the average among siblings, average in the level, average in the entire hierarchy, and average on leaves.

Getting ready

Start SQL Server Management Studio and connect to your SSAS 2012 instance. Click on the New Query button and check that the target database is Adventure Works DW 2012.

Here is the same initial query we used in the previous recipe for calculating various percentages:

WITH
MEMBER [Measures].[Level] AS
   [Sales Territory].[Sales Territory...