Dynamic and Nested Top N + Others in Power BI

by Inforiver | May 24, 2022 |

In one of our previous posts titled The Business Case for Top N + Others, we briefly looked into the following:

  • Top N Analysis and its significance
  • Preferred format for reporting: Top N + Others with grand total
  • Key variants of Top N
    * Nested Top N
    * Top & Bottom N combinations
    * N with numeric & percentage values
    * Dynamic Top N (with respect to how Top N is implemented in analysis tools)

In this blog, we will look at how Top N analysis can be implemented in Excel and Power BI. While looking at Power BI, we’ll explore both the traditional DAX-based implementation and the instant no-code implementation using Inforiver.

Implementing Top N analysis in Excel

Excel provides a lot of flexibility in how you can implement Top N analysis.

One option is to invoke Top N from the Pivot Table context menu.

Top N Excel

This shows a prompt where you can customize the settings to, say, Top 5.

Top N Excel Customization

This produces the following output.

Top N filtering in Excel - results

In our previous post, we learned why Top N analysis is served better with an Others row. However, the Pivot Table approach above does include an Others row by default.

To add an Others row, you need to use Power Pivot and DAX as outlined in this post Displaying Top N, Bottom N, and All Others by P3Adaptive.

You can also make your Top N semi-dynamic by using slicers. This offers better flexibility compared to the previous approach but is still not a fully dynamic solution.

If you are looking to keep things simple (without using Pivot Tables), you can just filter an existing table by using FILTER and LARGE functions and implement Top N. You can add the Others row and the grand total at the end manually.

Another interesting method to implement Top N in Excel is to use dynamic arrays.

While Excel delivers a variety of options, none of them are comprehensive and dynamic at the same time.

By comprehensive, we refer to the ability to:

a) deliver our preferred format of having an Others row with a grand total
b) list rows in descending order of value, except for the Others row
c) to perform nested combinations of Top and Bottom N
d) to support N for numeric or percentage values

By dynamic, we refer to the ability to

a) set Top N analysis quickly
b) switch between various values of N, and even between numeric and percentage values instantly (without modifying the script or layout)
c) switch between Top and Bottom N analysis instantly

We’ll now look at how Power BI implements Top N.

Implementing Top N + Others in Power BI

Just as with Excel, Power BI provides a few options to implement Top N too.

One way to filter Top N items is to use the filter pane. However, this does not provide an option to include the Others row in the matrix.

Power BI - 1 - TopN Native Filter

You can deliver advanced Top N + Others functionality by using DAX, as seen in this example from SQL BI. This approach adds an Others Row, with subtotals and grand total, and you can play around with various measures and values of N. 

While this repeats the Top N for each category, it is not a truly nested multi-level Top N (as the parent category is not restricted to Top N values).

Implementing Top N + Others

This approach is also not fully dynamic. The significant use of DAX also keeps such a solution out of reach for most Power BI users. Even the expert DAX users will find significant improvement in productivity by using the option outlined below.

Implementing a truly dynamic & nested Top N in Power BI

For a truly dynamic and comprehensive Top N implementation, let us explore a solution using Inforiver with the following characteristics:

a) It does not require users to code – you can set it up in seconds
b) It gives you the option to enable/disable Others row
c) It sorts values by default in descending order, except for the Others row that always appears at the bottom
d) It supports Top and Bottom variants for a given N
e) It supports both numeric and percentage values for N (e.g., Top 10 products or Top 5 percentage)
f) It supports multi-level and nested top N configurations (e.g., Top 10 products from Top 3 categories in the Top 2 regions = 60 records); also supports a combination of Top and Bottom N (e.g., Bottom 3 products in the Top 3 regions).
g) It is highly flexible and dynamic – you can change the configuration anytime or disable it in seconds

With Inforiver, you can set up a Top N filter anytime by clicking on the icon labeled ‘Top N’.

Inforiver - Top N ribbon toolbar

This opens a pop-up window. Fill in the following to filter the Top 10 items (to show the top 10% of items, just change the selection ‘Value’ to ‘Percentage’).

Top 10 Items by Sales

On clicking Apply, Inforiver immediately displays the following top 10 items with Others. It also indicates how many items are a part of the Others row (1628 items) – something that we have not seen in any solution yet.

Top 10 Items by Sales

Nested Top N + Others

To do a nested Top N, say Top 3 items in each of the Top 3 categories, create multiple rules by clicking on the ‘Add rule’.

Top 3 Items in Top 3 Categories - Nested Top N

The above setup displays the following output. Note that the Others row is available for both parent and child items.  In addition, since this is a nested hierarchy, it may be preferable to move the grand total to the top as shown below.

Top 3 Items in Top 3 Categories - Nested Filter

Multi-level Nested Top N + Others

You can increase the number of hierarchy levels even beyond two (2). Just add another rule to configure the third category.

Top 3 Vendors, Categories and Items

This configuration gives the following output. To keep the image compact, only STRAIGHT BOURBON WHISKIES are expanded to reveal its children.

Top 3 Vendors, Categories and Items

Qualifying ‘Others’ in Top N analysis

Can you think of any way in which the multi-level and nested implementation above can be enhanced further?

Well, if the report is going to require a scroll or is going to run into multiple pages, it may not be clear as to what a specific Others row refers to. Sometimes, having multiple Others in a single view may also cause confusion.

Nested Top N - Managing Others

To address this, Inforiver provides another option.

Nested Top N - Managing Others

This appends the category name to Others to each instance of others, except the one at the outer-most level.

Nested Top N - Listing Category Name with Others

A note on Bottom ‘N’ + Others

While we did not exclusively discuss Bottom N + Others, everything that applies to Top N equally applies to Bottom N.

With Inforiver, you can do a nested combination of Top and Bottom N. For example, you can set up the report to show the least sold items (Bottom N) belonging to the leading liquor vendors (Top N) as below.

Bottom N filter

This provides the following output.

Top and Bottom N filter

***

Top N + Others in Tableau

While Tableau is not a core focus of this post, Top N is a more commonly discussed feature in Tableau implementations. Given this, let us quickly look at how Tableau handles Top N and its variants.

Setting Top N with Tableau is not as quick and easy as it is with Inforiver as you still need to type in some formulae. However, implementing Nested Top N + Others in Tableau, while possible, requires a considerable amount of design effort.

For organizations looking to migrate their existing Tableau reports to Power BI, Inforiver offers a rapid migration path.

In Summary

To conclude, Top N + Others capability is not comprehensively addressed by tools in the market today. There is a gap that exists between what users need, and what the analytics tools can deliver, even after considering their scripting capabilities.

Inforiver delivers Top N + Others, and many more such capabilities using a powerful & intuitive no-code user experience. This delivers the power of Top N analysis and other such features into the hands of numerous users who are not conversant with DAX. Inforiver’s out-of-the-box capabilities also help organizations using reporting tools such as Tableau, Qlik, MicroStrategy, and Spotfire rapidly migrate their reports to Power BI.

Did you try out the Top N feature of Inforiver? If so, visit the Inforiver community and let us know what you think.


Inforiver Logo
About Inforiver!

Inforiver delivers the fastest way to build collaborative reports in Power BI through a seamless low-code / no-code experience. The product is developed by Lumel Technologies, which has a decade of experience in building visualization products including ValQ, xViz and BI Hub.
ValQ
BI Hub
xViz
5920 Windhaven Pkwy, #130
Plano TX 75093
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram