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.
RANKX(<table>, <expression>, <value>, <order>, <ties>)
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.
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.
(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.
(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.
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:
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.
Let’s change the order around and give the smallest number of Total Sales rank of 1:
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?
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.