Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# A Deeper Understanding Of Advanced RANKX

by | 1:00 pm EDT | August 24, 2020 | Power BI

This tutorial will go through an advanced example on the DAX function RANKX in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

This is a breakout session from the Learning Summit Workshop in Enterprise DNA Online.

The RANKX function gives you valuable insights when you use it for a range of different contexts in your report page. However, it can also be one of the most confusing functions to use in Power BI.

It can give you different results that seem to be correct, when in fact, they’re wrong. If you’re not careful, you’ll end up reworking the entire report.

## Relationship Between RANKX And Contexts

In this visualization, you can see the County Ranking, States Ranking, and State Regions Ranking.

Managing the context of the calculation can be difficult if you want to see them all in one matrix.

Let’s look at the table.

You can see it has information on the State Regions, State, and County.

For this example, you need to work out the ranking of the Counties.

If you just have the County, County Ranking, and Total Sales in the table, you get the correct ranking results:

But once you start to layer the other contexts in the table, the ranking result starts to accumulate each County that is in different States and State Regions.

So, you need to identify and change the context of the calculation to make sure you get the correct overall rank for each County regardless of what’s selected on the report page.

You can also make a selection using State Regions. If you select Northeast, it shows the proper ranking.

## Formula For County Ranking

In this tutorial, two formulas are used to get the County Ranking. This will help you understand the difference in the results generated by using different DAX functions along with RANKX:

### Formula #1: Using ALL And ALLSELECTED

The ALL function is the key part of this formula for County Rank:

It manipulates the context within the RANKX formula. It then iterates through the Regions table and creates the ranking result.

In this case, the ALL function is virtually working through every single County regardless of the initial context.

This problem becomes complicated when you start to layer on slicer contexts. By using ALLSELECTED, the ranking will automatically update depending on the selection in the slicer.

You also need to utilize CALCULATE to place additional context to a result or to change the context of the calculation.

The context is adjusted based on the filters within the CALCULATE function.

With this, the ranking will adjust based on the context coming from the State and State Region slicers.

### Formula #2: Using ALL And VALUES

Let’s now look at the County (Updated) column.

You want to make sure that this column will continue to show the overall rank regardless of what’s selected in the slicers. Every County should have a unique number.

This is the formula used:

For this example, there wasn’t a way to decipher a unique County. So, another column was created. The Full County column includes the County, State, and Region.

This gives you a good distinction between Counties. This way, you can now work a virtual table of the Counties that are in the data.

Before doing this, you need to remove the ALL context from the Regions table.

So, any context coming from the Region’s table doesn’t exist at this point in time. With the VALUES function, you can add back the context of the Full County column.

One option is to reference every column using the ALL function. But the better alternative is to get rid of all the context from Regions and only bring back the Full County.

Once the context has been manipulated inside CALCULATE, the RANKX function starts its work from there.

It works through every single County at every row along with their Total Sales, and then ranks them. That’s how you get the values of 1 to X under the County (Updated) row.

As a result, filters and slicers won’t matter and won’t affect the ranking because the formula has been set to ignore them.

## Conclusion

RANKX is not an easy function to use. The key is to focus around the nuances to be able to understand the function better.

Hopefully you have learned more about the advanced applications of the DAX function RANKX in Power BI.

You can check out the Mastering DAX Calculations course in Enterprise DNA Online for more discussions on the RANKX function.

All the best,

Sam

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## How to Interpolate in Excel: User Guide With Examples

In data analysis, interpolation plays a crucial role in estimating values that fall between known data...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...