Hierarchical variance analysis in Power BI

It’s not an exaggeration to state that organizations have hierarchies all around. Consider the following: 

• Regions - Country, State, City 
• Products – Categories, Subcategories, Products, SKUs 
• People – CXO, VP, Directors, Managers, Associates 
• Finance – General Ledger (GL) hierarchy 

When a business wants to assess performance for a particular region, product or department against a benchmark, it is often helpful to analyse variances while traversing through a hierarchy. More often, analysis hierarchies can also include a combination of entities: e.g., Region --> Product -->Sales Associate. 

This is where hierarchical variance analysis capabilities can come to help. 

***

To demonstrate this further, we’ll be using a sales performance dataset with 3 measures - 2021 actuals, 2021 plan and 2020 actuals and 4 categories – region, sub region, product category and subcategory. 

These are some of the hierarchical variance analysis capabilities delivered by Inforiver. 

1. Expand/collapse-enabled hierarchical table

You can view all the levels of data at once without the need to drilldown and expand or collapse to a particular level. Child count can be displayed next to the hierarchies. In addition, several settings are available for customizing the hierarchies. 

2. Absolute and % variance charts 

Absolute and relative variance are calculated automatically at every level of the hierarchy and can be visualized as bar and pin/lollipop charts.  

3. Customization options for totals and subtotals  

You can turn on/off or change the positions of the totals and subtotals using a no-code interface. Inforiver also provides a split subtotals option. 

4. Advanced conditional formatting 

Conditional formatting can be applied to rows, row headers, or columns based on the measures or variances. Formatting can be based on color scale, classification, rules (if conditions) or ranking. 

5. KPI explorer 

Inforiver’s KPI explorer can be used for filtering of rows, columns and measures. 

6. Nested filtering 

Filter rows using nested and/or conditions including filter groups.  

7. Top/bottom N + others

Inforiver supports multi-level and nested top N configurations (e.g., Top 5 subcategories from Top 2 regions); also supports a combination of Top and Bottom N (e.g., Top 3 subregions for the Bottom 3 subcategories). 

8. 2-measure and 3-measure bullet charts  

Compare 2 or 3 measures using 12 different bullet chart types. These charts can be inserted in a single-click and are highly customizable. Shown below are examples for the 2 and 3 measure bullet charts. 

9. One-click IBCS table templates 

International Business Communication Standards provides standard table templates for analyzing hierarchical data. With Inforiver, you can apply these templates in a single-click. Note that you only need to bring your data; the layout and formatting are automatically handled by Inforiver.​ 

The T01 template visualizes a measure (AC) with rows showing structural data and columns showing absolute and relative variances to previous year (PY) or plan (PL).​ 

The T02 template visualizes a measure (AC) with rows showing structural data and columns showing absolute and relative variances to previous year (PY) or plan (PL) as bars and pins.​ 

10. Sparkline charts for visualizing trend 

Sparklines can be added to the table by adding a time period in the column field and selecting a sparkline. The 9 available options include – line, area, win/loss, baseline and more.  

11. Percentage contribution columns 

Percentage contribution to parent/grand total can be inserted on-the-fly. Contribution or variance column can also be inserted with respect to a data point as shown in the 2^nd image. 

12. Manage aggregation 

You can override the standard measure aggregation for measures and apply custom aggregations such as average, standard deviation, minimum, maximum, and more in a single click. 

13. In-line notes and footnotes 

Notes can be added at a cell, row, column or row header level to provide descriptive details. These notes can be configured to display on hover or as footnotes.  

14. Excel-like pivot mode 

Inforiver supports expand/collapse hierarchies along both rows and columns like traditional pivot tables along with Stepped, Outline and classic pivot table layouts inside Power BI.   

In the below image, pivot mode is off, and this is the default view. Notice that the actuals, plan, and variances are grouped by year.

On choosing the ‘Values on Columns’ option, the columns are grouped by the measures – actuals, plan and the variances for the 3 years. 

On selecting ‘Values on Rows’, the matrix gets transformed as shown below with the measures on the rows and the years in the columns. 

15. Export to Excel and PDF 

Inforiver reports can be exported to Excel with expand/collapse capability, along with conditional formatting, scaled number formatting, Top N, filters​, notes etc. Here is a sample export. 

Inforiver also supports formatted and paginated export to PDF including the in-line charts. 

***

These are the 15 hierarchical analysis capabilities that we reviewed so far. 

Inforiver offers a lot more that what is listed above. You can try for yourself by installing the free trial version of Inforiver Matrix from Appsource.  

Inforiver Enterprise provides everything offered by Inforiver Matrix, along with several additional features such as manual data input, writeback, collaborative comments and report scheduler. Here is the Appsource listing for Inforiver Enterprise.