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.
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])|
|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]))),|
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.