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.

1 REPLY 1
technolog
Super User
Super User

Can you please tell me if the problem is still relevant? If you managed to solve it in some way, please write here how you solved this problem. Then it will be possible to mark this problem as solved.

If not, it may make sense to ask the question again, thus confirming the relevance of this problem.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.