Data Bear – Power BI Training and Consulting

Call us Today! (020) 8720 6880 | info@databear.com

How to use the DAX RANKX function in Power BI

How to use the DAX rankx function in power bi

RANKX DAX function in Power BI

The Power BI DAX RANKX function is a powerful sortation function. We will give an overview of what the RANKX function is capable of and make sure that you understand the basics. This is a super useful function so we will also explore some more complex methods of using RANX with practical examples.

RANKX DAX definitions and syntax

The RANKX function in simple terms returns the ranking of a specific number in each row of a table that forms part of a list of numbers. The RANKX function may be used in both calculated columns as well as calculated measures.

Syntax

RANKX(<table>, <expression>, <value>, <order>, <ties>)

<table>

The table parameter requires a table as input. This can be a standard table that you imported into Power BI or a calculated table through a DAX function. The RANKX function iterates through your table to rank a specific number in the same manner as SUMX, for example, will do.  (Please see this link to see our SUMX blog post) For example, if our table has 100 values the result of the RANKX function will have the smallest value of 1 and the largest possible value will be 100.

<expression>

The expression parameter requires any DAX expression that returns a single scalar value. The expression will loop through each row in your table and return your values to be able to rank them accordingly.  This may be a simple SUM function or some more complex calculations.

The following 3 parameters in the RANKX function is optional, meaning the above 2 parameters are key to do what is required to rank a specific column of numbers, but let’s have a look at what these optional parameters are for.

<value>

(Optional) The value parameter is a strange one in the context of RANKX. For most of your requirements for RANKX this may be omitted. When this parameter is used it may change the result of your values greatly. We will try and clear this out later in the post. 

<order>

(Optional) A value that specifies how to rank value, low to high or high to low. The default ranking if this parameter is omitted is in descending order. Meaning the highest value will get a rank value of 1. The only two values that can be passed to this parameter are ASC or DESC. Thus ASC will take the lowest value and give it a rank value of 1 and DESC will give your highest value a rank value of 1. 

<ties>

The ties parameter defines how to determine the ranking when there are ties in your values. Something to note here is that it will not ‘break’ ties, meaning that if we have two rank values of 3, it will not automatically rank it as a rank value of 3 and 4. This makes sense as the RANKX function does not know how to give preference when a tie does exist. RANKX will return tie values, meaning that if we have two rank values of 3, it will return a rank of 3 for both our values. Note here that there will be a ‘gap’ in the rank values in RANKX’s default state or if we pass SKIP into the ties parameter. For this example, the next rank value will be 5, as our rank values are 1, 2, 3, 3, 5.   If we pass DENSE in the ties parameter, it will not allow for gaps in the value of RANKX, meaning that in our used example the next rank value after our tied RANKX value of 3, will be 4. 

Practical RANKX examples

Let’s start off with the most basic RANKX example used in a calculated column.  Here we would like to rank our Total Sales for all rows in our table. Let’s use the following calculation:

RANX Total Sales =
RANKX(
‘Sales Table’,
‘Sales Table'[Total Sales]
)
Power BI DAX RANKX

Great, this gives us the result as expected. The new column ranked the Total Sales from the largest number to the smallest number, where the largest number has a value of 1. You will also note the ties here, let’s look closer at rank 4. You will note that for customer 9 and 3  sales were $243 each and have a rank of 4 and then it skipped 5 and ranked the following Total Sales of customer 6 a rank value of 6. This is a good first example of how the default parameters behave.

Let’s pass DENSE for the ties parameter to just confirm the behaviour of not skipping rank values.

RANX Total Sales DENSE =
RANKX(
‘Sales Table’,
‘Sales Table'[Total Sales],
,
,
Dense
)
Power BI DAX RANKX
It is important to take note of what the difference is now. The DENSE input does not skip any ranking values. Let’s again take the rank of 4, now we see that Customer 6 has a rank value of 5 rather than 6 as in the default or SKIP state of RANKX. So it is important to just make sure what method you would like to use so that your ranking values will make sense.

Let’s change the order around and give the smallest number of Total Sales rank of 1:

RANX Total Sales ASC =
RANKX(
‘Sales Table’,
‘Sales Table'[Total Sales],
,
ASC
)
Power BI DAX RANKX
Now we can see that by adding an order value of ASC, the RANKX function will start with the lowest value and give it a RANKX of 1. Again note the ties in our values.

Power BI Rank Within a Group Using the RANKX Function

Let’s now look at a more complex example. If we want to rank our Total sales value by a group we need to make some adjustments to our RANKX function. Let’s use in our case Country to make a group by which we would like to evaluate the rank per country. So our business question is what is the Total sales per Country?

Rank all rows as Column (Country) =
RANKX(
FILTER(
‘Sales Table’,
‘Sales Table'[Country ] = EARLIER(‘Sales Table'[Country ])
),
‘Sales Table'[Total Sales]
)
I have filtered our Table to display only values for the UK.
Power BI DAX RANKX
We can see from our results that the ‘Rank all rows as Column (Country) ‘ has ranked our Total Sales based on each Country. This means that our function will rank each Country beginning at 1. This is very useful and a very relevant use case. Let’s unpack our function and especially the FILTER and EARLIER DAX functions.

What FILTER is doing in this function is that it adds an additional row context, which in essence is a new table. So what FILTER will do is it will go and evaluate through every single row in our Sales Table and look at what the Country name is and evaluate it based on the same value as the EARLIER Country name. What the EARLIER, in essence, will do is if let’s say the UK was the first Country in our column it will iterate through the entire column and evaluate each row to see if it is the same as the first row, which is the UK. Once it iterated through the entire column it will create an additional row context against which the RANKX function will rank the values for only the UK. This iteration will then be performed for each Country and each Country’s ranking will start at 1.  Let’s take the UK as an example. Our original table has 99 rows when FILTER has iterated through the table based on the EARLIER function it will return a ‘new’ table to the RANKX function to evaluate containing only the corresponding rows based on the UK, 27 rows.
Let’s look at another example. We want to see what the ranking is for Total Sales based on Country. We will keep on utilizing our RANX function, but incorporate a few more DAX functions.
Breaking Ties
Let’s talk now to the situation of breaking ties so that we can have a sequential ranking of Total Sales per country. Our business question would be to see only the Top 5 sales for each country. In order to break these ties, we need to create a value that in essence does not have ties. 
I will add in our case a random value between 0 and 1 to each Total Sales value to ensure that each value is unique. In Power BI the RAND() function creates a random value between 0 and 1. Note here that this method assumes that you have no preference to which tie gets a higher or lower ranking. 
We will first create a new column called No Tie Column. We will simply add the Total Sales to the random value: 
No Tie column = ‘Sales Table'[Total Sales]+RAND()
Now we will create a new ranking column based on our newly created column, No Tie column:
RANX Total Sales (No Tie) =
RANKX(
‘Sales Table’,
‘Sales Table'[No Tie column]
)
Power BI DAX RANKX
We can see from the above solution that the ‘RANX Total Sales (No Tie)’ column creates a perfect sequential ranking. So what this comes down to is that you just need to create a way to ensure that you will have no tied values. 
That is it for this blog post, trust that it will all make sense and enable you to more fully utilize the amazing features that Power BI have. 
Please also see our other blog post for more amazing Power BI content. 
Check out our training, consulting and support packages for more information.

1 thought on “How to use the DAX RANKX function in Power BI”

  1. Hi

    Power BI Rank Within a Group Using the RANKX Function.

    How to use it as a measure. Because, unlike the calculated column, it is not supporting Earlier Function.

    Please could you provide feedback because the article said, “The RANKX function may be used in both calculated columns as well as calculated measures.” But I only see examples of Calculated Columns

Leave a Comment

Your email address will not be published. Required fields are marked *