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