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:

Shopping centres by size

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 RANKX function

The function sorts by the UNITS column.

 

You could then sort by this new Size order column:

Sorting a column

Click on this tool to sort by the column.

 

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

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:

The fourth argument

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

 

We could use:

Sorting in ascending order

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!

Ranking by a different value

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
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!

This blog has 0 threads Add post