How to use the RANKX function in PowerPivot
This blog shows how to use each of the 5 arguments of the RANKX function to rank rows in PowerPivot.

Posted by Andy Brown on 20 March 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Ranking in PowerPivot

There's always one question you forget to answer from a course ... yesterday someone asked me how you can rank data in PowerPivot.  The answer is to use the RANKX function.

## The Basic Function

Supposing that you want to rank shopping centres in size order, with the largest coming first:

Pavilion Shopping Centre is the 138th biggest by number of units.

The basic RANKX function looks like this:

=RANKX ( Table, Expression )

As such, it's similar to the SUMX, AVERAGEX and other aggregating functions.

So our formula could be:

The function sorts by the UNITS column.

You could then sort by this new Size order column:

Click on this tool to sort by the column.

The result shows there is a tie for 11th place:

Whitgift and The Harlequin both have 155 units, and so share 11th place.

## Changing the Default Sort Order

The fourth argument to the RANKX function controls the sort order.  The possibilities are:

Value What it means
0 or TRUE Sort in descending order (largest first), the default
1 or FALSE Sort in ascending order

Notice how the default sort order is different to that used in SQL!

You can see this fourth argument here:

We'll come to the Value argument in a moment.

We could use:

Note the extra comma to denote the fact that we've missed out an argument. Market Quay is now first in the list.

## Hypothetical Sorting

Just for the sake of completeness ... you can also specify where a row would appear in the sort order if it had a given value.  Here's a (rather strange) example!

Here we're sorting the centres as if the number of units in each was equal to the id of the shopping centre. I did say it was a bit strange!

The arguments above are:

Argument Contents What it means
1 tblCentre Rank the shopping centres table.
2 tblCentre[Units] Sort by the number of units in each centre.
3 tblCentre[Id] Substitute the id for each centre (see below).
4 True Sort in ascending order.

So the Pavilion Shopping Centre above has 62 units,  but we ignore this and pretend it had 1 instead (its id value).  If it did have 1 unit, it would be 2nd equal in the list, because there is one shopping centre with 0 units and the next smallest has 3 units.

## And finally - dealing with ties

The RANKX function has a 5th argument, which controls how ties are handled.  The easiest way to understand this is with an example:

 Skipping numbers Dense numbering

The default behaviour (left) is to skip numbers, so we go from 2 to 5 because there is a three-way tie for 2nd place.  If you use dense numbering, there are no missing numbers.

It's a bit disconcerting, as you don't get any help when you type in the enumeration for the 5th argument!