Managing Unbalanced (Ragged) hierarchies in Zebra BI Tables
A hierarchy is a systematic way of organizing the members of a dimension into a logical tree structure that defines parent-child aggregation relationships. Usually, hierarchies are balanced meaning that they have an equal number of levels in each branch of the hierarchy. If that is not the case, we are dealing with a ragged or unbalanced hierarchy which is a user-defined hierarchy that has an uneven number of levels.
Hierarchies are very useful for organizing more complex data sets and enables you more detailed data exploration with drill-down functionality. In the article Creating hierarchies in Power BI, you can learn how to create and use hierarchies in tables or chart visuals but all hierarchies in those examples are balanced.
If we are dealing with an unbalanced hierarchy this means that at least one branch that does not descend to the lowest level. Let us look at three examples of this can be:
- Time hierarchy that includes Year, Quarter, and Month, one branch might only have data down to the Quarter level.
- Company organizational structure with some branches in the hierarchy having more levels than others
- Income statement where some accounts have additional subcategories
Let’s look at the Income statement example where we have two-level accounts hierarchy but for two accounts, we do not have value for Account level. Displaying this in Power BI would result in “blank” fields.
In order to avoid this, we must fulfill an empty cell with (preceding) parent values. Now Zebra BI visuals will detect that we are dealing with an unbalanced hierarchy.
Now let us have a look at how the Account hierarchy will be displayed in Zebra BI Table visual. See how Gross profit and Operating income does not have a drill-down option despite having values on both levels of hierarchy? This approach makes your unbalanced hierarchies more organized and more user-friendly for end-users.
The example above was presented on a 2-level hierarchy, but the same approach can be used in the same way also on multilevel hierarchies. If you want to check how to create the example above, please check the article Hierarchy income statement.