Calculate Averages with Hierarchies

By - Updated May 28, 2015

There are more ways to aggregate values along a hierarchy than one would expect! In this article, we will use price as an example to explore how it can be aggregated along the Product hierarchy in four different ways. In the sample form below, users enter sales price at the SKU level. As we can see below, we have different ways to calculate the averages at the Brand and Category levels.

The following picture shows the formula for each calculation along the hierarchy.

In order to implement an Average calculation, one must first determine the correct behavior. Once the right behavior is decided, you will then need to leverage a combination of both MDX and SQL to implement within Kepion. Please see the following posts to learn more on how to implement the Average calculation.

1. Average by Non-empty Leaves
2. Average by All Leaves
3. Average by Non-empty Children
4. Average by All Children