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]
)
Image Source
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
)
Image Source
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
)
Image Source
- 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.
Take a look at the table below for an example:
Image Source
- 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] )
)
Image Source
- 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:
Image Source
- 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.
Image Source
- At this point, your previous example is no longer valid, as it now displays all of the names rather than just the top three.
Image Source
- You also show the Rank Name Simple measure to help you understand why this is happening.
Image Source
- 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.
Image Source
- 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.
Image source
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.
Image Source
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.
Image Source
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.
Image source
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.
Image Source
You can also use State Regions to make a selection. Selecting Northeast will show the proper ranking.
Image Source
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.
Image Source
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.
Image Source
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.
Image Source
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.
Image Source
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
Another wonderful method to use RANKX is to segment or group results rather than simply showing them without context.
Instead of just providing a general sales result, it would be preferred to know if a customer was among the top-ranked outcomes and how that changed over time. It would be preferred to find out how much of our sales come from our top five clients, the top five to twenty, and the rest of our clientele.
To determine if a trend exists, we must build dynamic groupings and examine them over time. This is really difficult to accomplish, especially if you’re working with Excel. However, if you understand DAX well, you may construct a formula that takes very little time to develop, and it will become a pattern that you can apply over and over.
Here are the measures developed for this scenario:
Image Source
We begin with the CALCULATE function because nothing else happens without it. Then we add the Total Sales, as that’s what we’re adding up.
Next, we filter over a table of Customer Names, which will cycle through each customer, evaluate their ranking, and then determine whether their ranking is greater than or less than the MIN and MAX that we just defined.
The next step is to employ the COUNTROWS and FILTER functions again. Then select Customer Groups.
Then, we’ll use RANKX to cycle through this table. We calculate Total Sales at the top, although the Customer Groups table truly ranks. We need to determine the rank of each of these individual customers and then check whether they belong to any of the categories listed in the Customer categories database.
We will then rank them by Total Sales, followed by DESC, with 1 being the highest and 200 being the lowest. We will then conclude with a higher than the MIN for the top-ranked clients. Finally, we’ll utilize RANKX using the same logic as before, but with less than or equal to MAX.
This DAX pattern can be used again in a wide range of applications. RANKX allows you to generate dynamic ranking tables, resulting in exceptional ranking insights.
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:
Conclusion
This article gives a detailed description of the RANKX Power BI function and how it is used in different examples. When calculating rank as a value in a Power BI visual, the RANKX Power BI scalar DAX function can be extremely useful.
Do tell us in the comments about your experience of working with Rankx in Power BI.
Harshitha is a dedicated data analysis fanatic with a strong passion for data, software architecture, and technical writing. Her commitment to advancing the field motivates her to produce comprehensive articles on a wide range of topics within the data industry.
1 -- https://res.cloudinary.com/hevo/image/upload/v1729852677/hevo-blog/ebook-downloadable-papers/ebooks/How_a_Modern_Data_Stack_Creates_a_360_Degree_View_pecmu0.pdf --- Download Your EBook For Free - Exit Intent Popup
