RANKX function (DAX) in Power BI

The RANKX function in Power BI is a dynamic and essential tool for sorting data in a Data Analysis Expressions (DAX) environment. Whether you’re identifying top customers, analyzing sales figures, or highlighting premier products, ranking is a fundamental calculation used across various industries. With the RANKX function, you can easily rank rows in a table based on a specified expression and criteria.

In this easy guide, we will walk through the basics of the RANKX Power BI function. You will learn the syntax and the parameters and look through multiple criteria to get exactly the ranked table you need. We will look at examples of using RANKX through examples and tables. Let’s begin!

What is Power BI?

Power BI is a proprietary Business Intelligence tool designed for seamless Data Analytics and Data Visualization. It is a part of the Microsoft Power Platform. Power BI is one of the common tools used by organizations for analyzing their business data construct reports. 

It comes with a collection of various in-built software services, apps, and connectors that deeply integrates with data sources to deliver immersive visuals, interactive reports, and generate insights. Power BI uses its advanced charts, graphs, and other visuals included with Machine Learning to easily extract valuable information out of data.

Power BI can also read data from XML files, CSV files, JSON format files, and even web pages, then convert raw data into interactive insights. It is available for Desktop, mobile, and on-premise servers. Users can create and share their reports with other Power BI users within the organization or partner companies. 

Key Features of Power BI

Some of the main features of Power BI are listed below:

• Supports API Integrations: Power BI allows developers to easily integrate with other applications and embed dashboards into other software using sample codes and APIs.
• Custom Visualization: Power BI offers custom visualization libraries support that allows users to visualize complex data with ease.
• AI Support: Users can easily perform Data Analytics using Artificial Intelligence. With the built-in AI support, users can prepare data, build Machine Learning models, and gain insights.
• Modeling View: With the help of Modeling View, Power BI users can slice and divide the complex data into simpler ones that help in better understanding the data, separate diagrams, and multi-select objects.
• Easy Sharing: Power BI makes it easier for users to easily share their reports within teams, or organizations ensuring full data protection.
• Hybrid Development: Power BI easily integrates with many 3rd party connectors, applications, and services widely used by organizations that allow users to connect to various data sources.

What is RANKX Power BI Function?

Data Analysis Expressions (DAX) is a programming language that can be used to create calculated columns, measures, and custom tables in Microsoft Power BI. It is a set of functions, operators, and constants that can be used to calculate and return one or more values using a formula, or expression. The DAX language has a function that returns the ranking of a sorted element based on a given expression. The RANKX Power BI is one such function; it is both a scalar and an iterator. The RANKX Power BI function is also a robust sorting tool.

The RANKX Power BI returns the ranking of a number in a list of numbers for each row in the table argument. 

The Syntax of the RANKX Power BI function is given below:

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

• The parameters are explained as follows:
  • Table: Any DAX expression that returns a table of data to evaluate over.
  • Expression: Any DAX expression with a single scalar value as the result. To generate all possible rankings values, the expression is evaluated for each row of the table.
  • Value: This is an optional parameter. Any DAX expression that returns a single scalar value with a rank needs to be determined. When the value parameter is not specified, the current row’s expression value is used instead.
  • Order: This is an optional parameter. A value that specifies whether the value should be ranked from low to high or from high to low:

value	alternate value	Description
0 (zero)	FALSE	Ranks in descending order of values of expression. If the value is equal to the highest number in expression then RANKX returns 1. This is the default value when the order parameter is omitted.
1(one)	TRUE	Ranks in ascending order of expression. If the value is equal to the lowest number in expression then RANKX returns 1.

• Ties: This is an optional parameter. When there are ties, an enumeration that defines how to determine ranking is used.

enumeration	Description
Skip	The next rank value, after a tie, is the rank value of the tie plus the count of tied values. For example, if five (5) values are tied with a rank of 11 then the next value will receive a rank of 16 (11 + 5). This is the default value when the ties parameter is omitted.
Dense	The next rank value, after a tie, is the next rank value. For example, if five (5) values are tied with a rank of 11 then the next value will receive a rank of 12.

• Return Value: For all rows of table numbers, the rank number of values among all possible values of expression is evaluated.
• Some things to consider while using the RANKX Power BI function are:
  • When an expression or value evaluates to BLANK, it is treated as a 0 (zero) for all number expressions and as an empty string for all text expressions.
  • If the value is not among the expression’s possible values, RANKX temporarily adds value to the expression’s values before re-evaluating RANKX to determine the proper rank of value.
  • Optional arguments can be skipped by using an empty comma (,) in the argument list, for example, RANKX (Inventory, [InventoryCost], “Dense”)
  • When used in calculated columns or row-level security (RLS) rules, this function is not supported when using DirectQuery mode.
• An example of the RANKX Power BI function is given below:

= RANKX(ALL(Products), SUMX(RELATEDTABLE(InternetSales), [SalesAmount])) 

The Sales ranking for each product in the Internet Channel is calculated by this column in the Products table.

Understanding RANKX Power BI Function

Understanding RANKX Power BI Function: Example 1

A simple example of the RANKX Power BI function is:

• You will begin with the most fundamental RANKX example in a calculated column. You will want to rank your Total Sales for all of the rows in our table here. Enter the following formula:

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

This gives you the expected result. The new column ranked Total Sales from the highest to the lowest, with the highest number having a value of 1. You’ll notice the ties here as well; take a closer look at rank 4. You’ll notice that sales for customers 9 and 3 were $243 each, with a rank of 4, and then it skipped 5 and ranked Total Sales for customer 6 with a rank of 6. This is a good starting point for understanding how the default parameters work.

• You can now test the behavior of not skipping rank values by passing DENSE for the ties parameter.

RANX Total Sales DENSE =
RANKX(
‘Sales Table’,
‘Sales Table'[Total Sales],
,
,
Dense
)

It’s critical to note what the difference is right now. There are no ranking values skipped by the DENSE input. Take the rank of 4 again, and you’ll notice that Customer 6 now has a rank value of 5 instead of 6 as in the default or SKIP state of RANKX. As a result, it’s crucial to decide which method you want to use so that your ranking values make sense.

• Rearrange the order and assign the lowest number of Total Sales a 1:

RANX Total Sales ASC =
RANKX(
‘Sales Table’,
‘Sales Table'[Total Sales],
,
ASC
)

• As you can see, adding an ASC order value to the RANKX Power BI function causes this function to start with the lowest value and assign it a RANKX of 1.

Understanding RANKX Power BI Function: Example 2

• When applied to a column with a specific sort order in the data model, the RANKX Power BI function may behave unexpectedly. The behavior of RANKX function is explained by the following example.
• You can use the Value numeric column in the Name column to perform a Sum Value ranking.

[Sum Value] :=
SUM ( Test[Value] )
 
[Rank Name Simple] :=
IF (
    HASONEVALUE ( Test[Name] ),
    RANKX ( ALL ( Test[Name] ), [Sum Value] )
)

• Here’s an example of why you should use a measure instead of a calculated column: You might want to use the current filters to display the top three names (you might filter the State column, for example). You can get the desired result using such a measure by taking advantage of the fact that tables and matrixes in Power BI hide the rows that return blank in all displayed measures:

[Top 3 Cases] :=
IF (
    [Rank Name Simple] <= 3,
    [Sum Value]
)

You get the following result:

• You notice, however, that the sort order isn’t what you expected. In this simple case, you could simply apply the proper sort order to the report’s column Top 3 Cases, but in more complex cases, the requirement may be different, and the sort order should be defined by another table column. In this case, you’d like to sort the Name column in ascending order by using the Value column from the same table. As a result, as shown in the dialog box below, you apply the Sort By Column to the Name column.
• At this point, your previous example is no longer valid, as it now displays all of the names rather than just the top three.
• You also show the Rank Name Simple measure to help you understand why this is happening.
• The RANKX Power BI function behavior appears to be broken when the Sort By Column setting is used. It appears to be a bug, but it isn’t. The reason for this is that Power BI creates a query like this for the previous report:

EVALUATE
TOPN (
    502,
    SUMMARIZECOLUMNS (
        ROLLUPADDISSUBTOTAL (
            ROLLUPGROUP ( 'Test'[Name], 'Test'[Value] ), "IsGrandTotalRowTotal"
        ),
        "Top_3_Cases", 'Test'[Top 3 Cases]
    ),
    [IsGrandTotalRowTotal], 0,
    'Test'[Value], 1,
    'Test'[Name], 1
)
ORDER BY
    [IsGrandTotalRowTotal] DESC,
    'Test'[Value],
    'Test'[Name]

As you can see, the Rank Name Simple measure is calculated in a row context created by iterating two columns from the table Test: Name and Value. This happens regardless of the table’s column settings. In fact, before the measures, you only included the Name column in the report.

When you enable the Sort By Column setting, Power BI includes the corresponding sort column in the same DAX query every time you include a column in a report. For most aggregations, this isn’t an issue because the two columns should have the same granularity. When a context transition (the measure [Sum Value]) occurs during an iterator (the RANKX Power BI function), the presence of the additional sort column (Value) in the row context that evaluates the measure generates a filter context that includes a filter over the same Value item for all the rows iterated by RANKX Power BI function.

• When applying RANKX Power BI function to a single column, you can avoid this problem by including the sort column. For instance, you could write:

[Rank Name] :=
IF (
    HASONEVALUE ( Test[Name] ),
    RANKX ( ALL ( Test[Name], Test[Value] ), [Sum Value] )
)

• This ensures that the context transition produced by Sum Value in RANKX will override the filter context in the query generated by Power BI for both the Name and Value columns. This strategy yields the expected outcome:
• If we remove all of the unnecessary measures from the report, we can now see the top three names.

• If you’re using the RANKX Power BI function, keep in mind that sorting by column in the data model might break an existing measure. To generate the correct measure in DAX, you should know that there is a sort by column condition. This issue should not affect iterators over physical tables, but the table granularity may not be appropriate for your calculations, and the context transition when iterating a table may be slower.

Advanced RANKX DAX Example

The RANKX function provides you with valuable insights when you apply it to different contexts on the report page. However, it can be a very confusing function in Power BI. You can get different correct results that in fact are wrong. If you do not take careful measures, you might need to rework your entire report.

You should have a solid understanding of the context and then apply RANKX in that context.

This example shows how complex the RANKX function can be. It’ll also help you understand the many nuances of working with RANKX.

The image below shows the County, State, and State Regions Ranking.

It can be difficult to manage the context of a calculation if you want to view everything in one matrix. Let us look at the table. It contains information about the State Regions, the State, and the County.

In this example, you must determine the ranks of the counties. If you only include the County, the County Ranking, and the Total Sales in the table, you will receive accurate ranking results.

However, as you add the other circumstances in the table, the ranking results begin to build for each County located in different states and state regions.

As a result, you must identify and adjust the context of the calculation to ensure that you get the accurate overall ranking for each County, irrespective of what is selected on the report page. When you select a state, such as Colorado, the formula also adjusts.

You can also use State Regions to make a selection. Selecting Northeast will show the proper ranking.

We have used two formulas in this example to get the County Ranking. 

Other Practical RANKX Measure Power BI Examples

Now that you’ve covered the fundamentals of RANKX and Power BI DAX ideas, let’s look at some other examples of how RANKX is used. First, consider an example in which RANKX is used to determine the highest and lowest values. The following example shows how to segment or group results using RANKX.

Finding Top And Bottom Values Using RANKX

RANKX is commonly used to identify top or bottom values, such as top customers or bottom products when combined with other DAX functions. In this example, we’ll use RANKX with CALCULATE and FILTER functions to identify our top customers over time and display them in a sales matrix.

In this first example, we’ll use RANKX in conjunction with the CALCULATE and FILTER methods to determine who our top customers are not only during a set period of time but also over time. Then present it in a sales matrix for our top ten customers. 

When we combine the Quarter & Year and Total Sales measurements in a matrix, we get all of the findings. This is excellent, but it can be difficult to gain insight and identify trends.

We want to identify trends and establish who our top ten clients are during this time period. For example, did we have a client who was formerly one of our top customers but has since dropped off completely? These are the types of insights we are looking for.

We can use a single formula for this. Here’s the measure, built with the compute function to compute our Total Sales for the top ten clients.

Top 10 Clients => 
                       CALCULATE( [Total Sales],
                                FILTER( VALUES (Customer [Customer Names]), 
                                           IF(RANKX(ALL (Customer [Customer Names]), [Total
Sales),, ASC) <= 10, [Total Sales], BLANK())))

We must utilize ranking logic to develop a ranking logic indicator that states whether a customer is in the top ten, if Yes, then return the Total Sales figure. In addition to using RANKX in Power BI, we will use the FILTER function followed by VALUES, which will generate an internal table for each individual customer.

Then we need to consider what reasoning to write here, such as “if the current rank is less than or equal to 10, equal total sales; if not, equal blank.” The ability to write this type of logic makes filter statements great.

So, we’ll utilize the RANKX function with ALL client Names to rate each client in any given scenario. The ALL removes any filters on Customer Names, allowing us to complete this ranking.

We will next rank by total sales in descending order, followed by less than or equal to 10. If it does, we want it to equal Total Sales; otherwise, it should go blank.

Top 10 Clients => 
                       CALCULATE( [Total Sales],
                                FILTER( VALUES (Customer [Customer Names]), 
                                           IF(RANKX(ALL (Customer [Customer Names]), [Total
Sales),, ASC) <= 10, [Total Sales], BLANK())))

Finally, we will substitute Total Sales for our Top 10 Clients. We can check the top 10 clients for each time period and use conditional formatting to make it more visually appealing. We can also utilize conditional formatting to provide more convincing insights.

If you sell to clients frequently, you should decrease the timeframe; nevertheless, if you only sell to customers twice or three times a year, you should analyze things over a longer time frame.

TOPN is another DAX function that can be used to rank insights. However, there is still a significant difference between TOPN and RANKX. TOPN is a table function, whereas RANKX returns a scalar. The key is to understand the environment in which you place these functions.

Combining Dynamic Grouping Using Power BI RANKX

RANKX Power BI is a great tool in the dynamic ranking and segmentation of data, to segment sales of customers you can have a formula of DAX that is based on the function of calculation using CALCULATE as this is something which should be done for changing the filter context in the process. It sums Total Sales while looping over each customer table. It filters to rank each customer based on their sales and categorizes them into specific groups, like top clients, middle-range clients, and others.

The formula makes use of `COUNTROWS` and `FILTER` to narrow down which customers belong to specific groups and then applies `RANKX` to rank these customers based on their total sales in descending order. Ranking begins from 1 (highest) and continues downwards. Define minimum and maximum ranks, allowing you to categorize your customers in categories like top 5, 6-20, and so on. It enables you to monitor the ranking over time and find the trend in customer sales. 

This method is very reusable and can be used in different places for generating dynamic ranking tables and getting insight into sales performance.

Troubleshooting Common Issues with RANKX in Power BI

Some problems you might encounter while using RANKX in Power BI include missing or incorrect parameter values, incorrect data, formula errors, and wrong syntax. If you run into any problems, double-check your syntax, parameter values, and formula to ensure that your data is complete and exact.

Rank Within a Group in Power BI Using the RANKX Function

Let us now look at an advanced example. If you want to rank your Total sales value by group, you need to make some changes to the RANKX method. You can use Country as an example to create a group to analyze the rank of each country. So, your business inquiry will be: 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]
)

The results show that ‘Rank all rows as Column (Country)’ sorted the Total Sales by Country. Each country will be ranked beginning with 1. This is really useful and relevant.

Using Rankx to Analyze Data Across Multiple Categories

RANKX can be used to analyze data from several categories. For example, if you wish to compare sales by product and location, create a table that displays the rank of sales for each product and region, and then use RANKX to determine the rank using both columns. This allows you to find the best-performing categories and areas, as well as visualize trends and patterns.

You can also use RANKX to analyze data from multiple time periods. Measuring the rank of sales over time allows you to detect trends and patterns that might not be obvious in your data. This can allow you to make more educated business decisions and discover areas of improvement.

How to Create Custom Aggregations with RANKX in Power BI

You can create custom aggregation using RANKX using several measures or columns. For example, if you are looking to create a new measure to show the average sales value for the top 100 products by sales value, you can use RANKX to rank all products by sales value, then filter the results to show only the top 100 products, and finally calculate the average sales value.

Another example to construct custom aggregations using RANKX is calculating the cumulative sales total for every product over time. You can do this by using RANKX to rank each product’s sales by date, followed by the RANKX function to compute the running total. This allows you to track how each product’s sales have increased or decreased over time, as well as find trends or patterns in the data.

Here are some essential reads for a deeper dive into Power BI functions:

• All about the Power BI Calculate Function Simplified 101
• Power BI Aggregations: Simplified 101
• How to do Power BI SUMIF in DAX: 2 Easy Functions
• COUNTIF Function in Power BI: 4 Critical Methods

Conclusion

The RANKX function in Power BI is one of the most powerful functions for ranking data across various categories and time periods. With this function, businesses can derive valuable insights about how different products, customers, or sales teams are performing. It can rank total sales, analyze trends, or segment results into dynamic groups. RANKX is flexible enough to create meaningful rankings and visual patterns. Its parameters, such as sorting order, ties, and filter context, must be understood to ensure accurate results.

Hevo Data provides an Automated No-code Data Pipeline that empowers you to overcome the above-mentioned limitations. Hevo caters to 150+ data sources (including 60+ free sources) and can seamlessly transfer your data within minutes. Try a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also, check out our unbeatable pricing to choose the best plan for your organization

FAQs