In this short post, we will explore how to create a DAX measure for Top N ranking with drill down.

Purpose of this DAX Measure

In many business scenarios, understanding the performance of products at different levels of a hierarchy (such as Category, SubCategory, and Product) is crucial. The ‘Top N Ranking with Drill Down’ measure in DAX allows users to dynamically rank products at each level of this hierarchy based on their sales performance. This dynamic capability means that as you drill down from Category to Product, the ranking adjusts to reflect the top N items at that specific level.

The data and the model

To illustrate our example we’re going to use the ContosoRetailDW sample database. I have loaded the fact sales and the dimensions Product which contains the Product Hierarchy. There’s also a Top N parameter that we will use later to make our measure more dynamic.

Result Expected

In our scenario, we want to display the top 5 categories with the ability to drill down to subcategories while still showing only the top 5 subcategories and the same for the top 5 products.

As we can see above our measure Top 5 Products only shows the top 5 Categories and same for the top 5 subcategories.

Let’s now’s break down the DAX measure

1. Defining the Ranks

The measure begins by defining three different ranks for each level of the product hierarchy:

var _rank=5
var _rankLevel1 =RANKX(ALL(dim_product[ProductCategory]), [Total Sales], , DESC, Dense)
var _rankLevel2 =RANKX(ALL(dim_product[ProductSubCategory]), [Total Sales], , DESC, Dense)
var _rankLevel3 =RANKX(ALL(dim_product[Product]), [Total Sales], , DESC, Dense)

Each RANKX function is ranking items (Category, SubCategory, Product) based on the Total Sales measure. The ALL function removes any filters that have been applied to the column.

2. Applying Context with ISINSCOPE

The ISINSCOPE function checks the level of detail in the current context, so it will return true when the specified column is the level in a hierarchy of levels

• ISINSCOPE(DIM_Product[Category]) –> returns True when we are in the category level
• ISINSCOPE(DIM_Product[SubCategory]) –> returns True when we are in the subcategory level
• ISINSCOPE(DIM_Product[Product]) –> returns True when we are in the product level

This is crucial for the drill-down functionality as it determines whether the user is viewing data at the Category, SubCategory, or Product level.

3. The SWITCH Function

The final part of the measure uses the SWITCH function:

SWITCH(true(),
ISINSCOPE(DIM_Product[Product]),IF(_rank3 <=_rank,[Total Sales],BLANK()), ISINSCOPE(DIM_Product[SubCategory]),IF(_rank2 <= _rank,[Total Sales],BLANK()), ISINSCOPE(DIM_Product[Category]),IF(_rank1 <= _rank,[Total Sales],BLANK())
 )

This part is where the magic happens. Depending on the current context (determined by ISINSCOPE), it displays the Total Sales for the top 5 items in that context. If the rank is greater than 5 , it returns BLANK(), effectively filtering out those items from the visual.

And here the trick is to start from the lowest level in the hierarchy. If we start from the highest level, the Top N rank with drill down will not work correctly or will only work for the first level in the hierarchy since the first condition will always be true and the next condition will never be evaluated by the switch function.

Dynamic Paramater

Finally, to make our DAX measure Top N ranking with drill down more dynamic, we can simply add a field parameter “Top N”.

And here is the final code with the Top N parameter:

rankkproducthierarchies = 
var _rank=SELECTEDVALUE('Top N'[Top N])
var _rankLevel1 =RANKX(ALL(dim_product[ProductCategory]), [Total Sales], , DESC, Dense)
var _rankLevel2 =RANKX(ALL(dim_product[ProductSubCategory]), [Total Sales], , DESC, Dense)
var _rankLevel3 =RANKX(ALL(dim_product[Product]), [Total Sales], , DESC, Dense)
RETURN
SWITCH(true(),
        ISINSCOPE(dim_product[Product]),IF(_rankLevel3 <= _rank,[Total Sales],BLANK()),
        ISINSCOPE(dim_product[ProductSubCategory]),IF(_rankLevel2 <= _rank,[Total Sales],BLANK()),
        ISINSCOPE(dim_product[ProductCategory]),IF(_rankLevel1 <= _rank,[Total Sales],BLANK())
)

To make the chart title dynamic we can also follow the solution that I described https://datakuity.com/2018/02/02/powerbi-dynamic-chart-title/ (this is an old post but the measure title is still the way to go as of today, except that we no longer to use the label trick)

Conclusion

There are likely several other methods to create a measure like this, such as using a calculated table. However, what I like with this approach is that it is straightforward to understand, easy to maintain, to adapt and customized for many scenario.