BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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!