Calculating levels in a hierarchy for dashboard filtering

Summary

We explain here a dynamic way to filter specific levels of a hierarchy. This provides a better way to filter & visualize hierarchies.

Overview

This tutorial explains how to calculate the level of a list in a hierarchy in order to apply specific calculations (custom summary) or filters by level.

In this example we have an organization hierarchy of 4 levels (Org L1 to Org L4). For each item in the hierarchy we want to calculate a filtering module value that returns the associated level.

Context and notes

• This technique addresses a specific limitation within dashboards where a composite hierarchy's level cannot be selected if the list is synchronized to multiple module objects on the dashboard.
• We show the technique of creating a static filtering module based on the levels of the composite structure.
• The technique utilizes the Summary method Ratio of line items corresponding to the list levels to define the value of the filtering line items. Note that it is not a formula calculation but a use of the summary method Ratio applied to the composite hierarchy.

Example list

We defined in this example a 4-levels list as follows:  Defining the level of each list

In order to calculate the level of each item in the lists, we need to create a module that calculates it by:

• Creating as many line items as level of hierarchy + one technical line item.
• Changing the settings in the blueprint of those line items according to the following table: Line Item Formula Applies to Summary Summary method Setting Ratio Technical line item 1 (empty) Formula Level or L4 (lowest level) 4 Org L4 Ratio* L3 / Technical L3 3 Org L3 Ratio L2 / Technical L2 2 Org L2 Ratio L1 / Technical L1 1 Org L1 Ratio L1 / Technical

When applying these settings, the calculation module looks like this:

*Note that the Technical line item Summary method is using Formula, Minimum Summary method can be used but will return an error when a level of the hierarchy does not have any children and the level calculated is blank. We can now use the line item at the lowest level—“Level (or L4)” in the example—as the basis of filters or calculations.

Applying a filter on specific levels in case of synchronization

When synchronization is enabled, the option “Select levels to show” is not available. Instead, a filter based on the level calculated can be used to show only specific levels.

In the example, we apply a filter on the level 4 and 1: This gives the following result: The content in this article has not been evaluated for all Anaplan implementations and may not be recommended for your specific situation.