Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Using RankX in DAX For PowerPivot / PowerBI

A common reporting need is to rank a certain Attribute by a value.  In DAX, the syntax is the following:

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

 

As always, you can also reach me by going to Bipatterns.com, via connecting on LinkedIn, or in the PowerBI Community. Feel free to leave an endorsement on my LinkedIn if any of my material helps.

 

Parameters

table
Any DAX expression that returns a table of data over which the expression is evaluated.

expression
Any DAX expression that returns a single scalar value. The expression is evaluated for each row of table, to generate all possible values for ranking. See the remarks section to understand the function behavior when expression evaluates to BLANK.

value
(Optional) Any DAX expression that returns a single scalar value whose rank is to be found. See the remarks section to understand the function’s behavior when value is not found in the expression.

When the value parameter is omitted, the value of expression at the current row is used instead.

order
(Optional) A value that specifies how to rank value, low to high or high to low

Let’s walk through an example, where I have the following data model and want to Rank each Manufacturer by their total sales.

Contoso Rankx Data Model

When you start writing RANKX Measures, there are a couple common complications to be aware of.  The most common attempt people will make the first time they write a RANKX Measure looks something like this:
Wrong Rank by Manufacturer :=
IF (
    HASONEVALUE ( DimProduct[Manufacturer] ),
    RANKX ( DimProduct, [Total Sales] )
)

However, when we put this on a pivot table we see it doesn’t work as expected.

Wrong RankX

The issue here is the first value in the RankX function doesn’t have an ALL(), which means each manufacturer is being ranked against itself (hence the 1 value in each row).

The second RANKX mistake I see a lot is this:
Wrong Rank (Without Calculate) :=
IF (
    HASONEVALUE ( DimProduct[Manufacturer] ),
    RANKX ( ALL ( DimProduct )SUM ( [TotalCost] ) )
)
This expression returns the same results we saw with the first calculation.  The DAX fformula is aggregating rows without a Calculate wrapping it up, so the row context is not transformed into a filter context.

Finally, we get to the correct methods for using RANKX.
Rank by Manufacturer :=
RANKX ( ALL ( DimProduct[Manufacturer] ), [Total Sales] )

Rank by Manufacturer (Excluding Grand Totals) :=
IF (
    HASONEVALUE ( DimProduct[Manufacturer] ),
    RANKX ( ALL ( DimProduct[Manufacturer] ), [Total Sales] )
)

Rank by Visible Manufacturers :=
IF (
    HASONEVALUE ( DimProduct[Manufacturer] ),
    RANKX ( ALLSELECTED ( DimProduct[Manufacturer] ), [Total Sales] )
)

Putting these DAX Measures on a pivot table we see the correct results:

RankX Correct Measures

We can more clearly see the differences in the Ranking calculations when we filter the Manufacturer’s down to just a few.

Filtered RankX

There is a noticeable (and hopefully intuitive) difference in the Rank by Manufacturer measure and the Rank by Visible Manufacturers.  The former is still ranking each manufacturer against ALL manufacturers, while the latter is only ranking the manufacturer in the row against the remaining Manufacturers in the Pivot table.

 

Go to Bipatterns.com for more techniques and how to guides.

13 REPLIES 13
Anonymous
Not applicable

@Anonymous Best example of how to use RANKX that I have come across, grateful you provided 3 samples of usable DAX including the version that removes the grand total. Thanks so much for posting!

SamTailor
Helper I
Helper I

God bless the author of this subject! Thanks a lot, it helped me!

Anonymous
Not applicable

Hi 

 

What needs to be done if we want to find the Rank based on 4 columns like the way we can do in SQL using Row_Number (By Partition) eg. Row_NUMBER() OVER ( Partition By SurrogateID , MeasureID, NetAggPartID ORDER BY TINAgg ASC) 

 

Thanks

Pallavi
Helper II
Helper II

Clearly understood the difference. Thanks.

 

I am using Rankx function on the values that ranges from positive to negative integers that are outcome of a measure doesn't give rank in order. It fails at the point when negative value starts,

 

Balance                     Rank

 

594                              1

121                              2

120                              3

106                              4

82                                5

18                                6

-28                              8                     --> should be 7

-64                              9                     --> 8

-73                             10                    -->9

 

what to do in these type of scenarios? Please help

 


@Pallavi wrote:

Clearly understood the difference. Thanks.

 

I am using Rankx function on the values that ranges from positive to negative integers that are outcome of a measure doesn't give rank in order. It fails at the point when negative value starts,

 

Balance                     Rank

 

594                              1

121                              2

120                              3

106                              4

82                                5

18                                6

-28                              8                     --> should be 7

-64                              9                     --> 8

-73                             10                    -->9

 

what to do in these type of scenarios? Please help

 



I'm struggling to resolve this as well, did you get any help? 

Sean
Community Champion
Community Champion

Okay so the reason I think no one else has chimed in here is because you need to provide more information and some context

For example what is the Measure formula that produces the Balance figure?

Also what additional fields are in the table/matrix (Visual that shows output)

The sample data provided is not enough since it actually produces the correct result with a very simple sum measure

Rankx Demo.gif

Anonymous
Not applicable

@Anonymous Can you please show the correct way to write the RANKX function when needing to use the CALCULATE function? You showed that using something like this:

IF (
    HASONEVALUE ( DimProduct[Manufacturer] ),
    RANKX ( ALL ( DimProduct ), SUM ( [TotalCost] ) )
)

is incorrect, but I didn't see anywhere where you showed the correct way of writing that formula. This happens to be something that I'm currently trying to do and I have yet to find anyone who's shown how to correctly write this formula (myself included). An example would be tremendously appreciated.

 

In case it'd be helpful, here is the current form of my code:

rankImpressions = 
RANKX(
	ALL('Weekly Summary'),
	SUM('Weekly Summary'[Impressions]),
	,,
	ASC
) 

When you throw the result into a table, all of the Rank Values return as "1". What is it that I need to do to fix this?

Sean
Community Champion
Community Champion

@Anonymous

CALCULATE is needed to perform the context transition for each row of the table

otherwise the filter context is always the same and all items will rank 1

rankImpressions = 
RANKX(
	ALL('Weekly Summary'),
	CALCULATE ( SUM('Weekly Summary'[Impressions]) ),
	,
	ASC
) 

However if you already have a Measure for SUM( 'Weekly Summary'[Impressions] ) then you don't need the CALCULATE

rankImpressions = 
RANKX(
	ALL('Weekly Summary'),
	[MEASURE],
	,
	ASC
) 

Hope this helps! Smiley Happy

Bsantos
Regular Visitor

Hi, when I try to open your file I get a mistake not found 404 error.... can you help me ?

Greg_Deckler
Super User
Super User

@Anonymous - This would make a great Blog post, you should consider signing up for the Power BI Community Blog site.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi @Greg_Deckler,

 

I appreciate the suggestion.  I've reached out to @MiguelMartinez yesterday and I'm hoping to be able to contribute to the community blog soon.  I see a lot of users writing requests for help with the similar issues, and think I could write some good tutorials which will help out.

 

Thanks again!

@Anonymous @Sean can you provide some suggestions to my RANKX and FILTER based question here https://community.powerbi.com/t5/Power-Query/RANKX-excluding-blanks/m-p/805465#M27020

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.