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 Parent %, Level %, Hierarchy %, and Leaves %. We used the ragged Sales Territory hierarchy of the Sales Territory dimension. There is a good introduction to ragged hierarchies 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 2016 instance. Click on the New Query button ...

Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.