Problem
We can use the DAX RANKX function to return the rank of an item based on one of its numerical attribute. For example, we can create a measure using RANKX function to calculate the ranks of the product categories based on the order quantity within the product categories.
This measure will normally work fine until we drill the product hierarchy down to next level. As the rank measure is calculated at the parent-level, it will not work at the child-level.
Solution
To ensure the rank measure working at both parent-level and child-level, we need to calculate the rank accordingly depending on the what level the current filter context is at. We can use the DAX ISFILTERED function to evaluate current level.
In the example below, we created two measures, [Category Filtered] and [Product Filtered], using the ISFILTERED function. At the product category level, we can see only the Product Category column is filtered.
When we drill the table down to the product level, both product category and product columns are filtered.
We can now create a single measure to return the correct rank at both hierarchy level:
| Rank by OrderQty = | |
| VAR IsProductFiltered = ISFILTERED('Product'[Product Name]) | |
| VAR IsProductCategoryFiltered = ISFILTERED(ProductCategory[Product Category]) | |
| RETURN | |
| IF (IsProductFiltered && IsProductCategoryFiltered, | |
| RANKX(ALL('Product'[Product Name]), CALCULATE(SUM(SalesOrderDetail[OrderQty]))), | |
| IF (NOT IsProductFiltered && IsProductCategoryFiltered, | |
| RANKX(ALL(ProductCategory[Product Category]), CALCULATE(SUM(SalesOrderDetail[OrderQty]))), | |
| BLANK() | |
| ) | |
| ) |
We first use the ISFILTERED function to evaluate which level is the current context at and then use the RANKX function to calculate the ranks accordingly. As the snapshot below shown, the rank will work fine at both levels.
Azure Data Ninjago & dqops 
this is great!! how can we enhance this so that if i pick a product I can drill through to another page that will provide products details and summaries of what was chosen. Then have card display which will show the rank x of x from the data provided from where we drill from???
I think maybe we have to use a summarize or calculatetable in a measure….but i have had no luck so far
But at total level ao can we sum the total of TOPN values depending on what level we are?
ate total level we can’t test ISFILTERED ou ISINCSOPE because there now row context?
Thanks for answer.
José Pintor
What if we need to add another couple of levels? Thanks ahead!
Is it possible to have additional level…for ex. more than 2 hierarchy levels