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:

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:

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:

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.