The DAX language in Power BI has a function RANKX that returns a ranking of a number in a list of numbers for each row in the Table argument. RANKX is a scalar function and it is also an iterator. The RANKX function can optionally take a Value argument that represents a scalar value whose rank is to be found. The optional Order argument specifies how to rank Value, descending (0) or ascending (1). The optional Ties argument defines how to determine ranking when there are ties. Skip (default) will use the next rank value after a tie, and Dense will use the next rank value (i.e. there will be no gaps in the rank numbers).
The following example illustrates using the RANKX function to rank the sales by product color when RANKX is used in the measure calculation. The simplest use of this function is the following:
To use the RANKX, Let’s first create TotalSales Measure in the below data model:
In this example, the RANKX function compares the value of the TotalSales measure for each row in the PivotTable against the values of the TotalSales measure for all rows of DimProduct[ProductColor] and thus producing the rank for the current row.
When the PivotTable rows are sorted by TotalSales, the rank calculation becomes clearer as shown in the screenshot.
Let’s see this in action with the scenario on calculating rank of the products based on their profitability.
We will look at how we can use RANKX to create a measure in the same Power BI data model that provides the ranking of the Products based on their profitability.
As you learned before, RANKX is a DAX Function that allows you to calculate the ranking of a number associated to the item/product in a table.
Our goal is to create the ranking of each product based on their profitability. The idea is that in the current filter context we have product and we want to compare this product with all the other products. So the first argument is the table that we want to iterate to see the values of the other products that we want to compare with.
So the first argument is the DimProduct table that has one to many relationships with FactSales Table with the ProductKey in the DimProduct table being the primary key and ProductKey in the FactSales as the Foreign Key. At this point, the second argument is the measure that we want to compare and we will use the Profit Measure for the second argument.
Profit:= SUMX(FactSales, FactSales[SalesQuantity] * (FactSales[NetPrice] – FactSales[UnitCost]))
At this point, if you look at the result in the pivot table, it will only give you the value 1 on Profits Ranking.
So there is clearly something wrong in our formula.
Let’ dig into how RANKX works and how it performs these calculations.
It first builds the lookup table where it computes the Expression argument (second argument) in a row context on the table specified in the first argument. In other words, one value for each row during the iteration and then sorted by the Expression.
It also computes the Expression for the Current Filter Context (measure)and ultimately finds the position based on the Expression in the lookup table.
On the closer look up, we can see that both Table & Expression arguments are evaluated in the different contexts:
- It is evaluated in the external filter context
- It is evaluated in the external filter context coming from the measure calculations for the rank calculation
- It is also evaluated in the row context of Table generated during the iteration.
It means that the Products are evaluated in the filter context of the cell in the pivot table and during the calculation of the rank, the look up table (DimProduct) only have a single row for the evaluation of the filter context.
How to fix this?
Supplying ALL or ALLSELECTED in the first argument removes any existing filter context from the DimProduct table. This forces all the rows in the look up table to become available for the evaluation of the second argument and the RANKX compares each product with value of all the products.
However, there is still something strange at the GRAND TOTAL level where we can see value 1.
We can remove this by applying a check condition using IF and HASONEVALUE.
This check condition evaluates that If only one product is selected, we perform the ranking evaluation but if in case more than one products are selected, the formula skips the evaluation of the RANKX and returns the blank value.
At the GRAND TOTAL level, all the products are included and thus IF and HASONEVALUE will return blank value of the cell in the pivot table.
Next time, we will look at using RANKX to calculate the static ranking in the calculated columns.