I was prompted to write this post based on a question in the SQL forums.
There is a single table of data containing thousands of transactions by Customer, Year, Origin, Type. The measure is Sales.
The output needs to show the Rank of each Customer based on Sales for the subset of data defined by the Year, Origin & Type.
Without Power Pivot this would be quite painful to create but with Power Pivot 2012 we have the new RANKX function.
The RANKX takes the following arguments :
This can be any DAX expression that returns a table of data.
This can be any DAX expression that returns a single value. The expression is evaluated for each row of table, to generate all possible values for ranking.
Any DAX expression that returns a single scalar value whose rank is to be found. If this is left out the expression is used as the basis for the rank.
0 = Descending order (the default)
1 = Ascending
When there entities with the same rank (ties) we can set a particular behaviour as follows :
Skip = If there were 4 entities tied at the rank of 3 the next rank will be 7 (4+3)
Dense = If there were 4 entities tied at the tank of 3 the next rank will be 4.
In this example we create the rank measure :
=RANKX(ALL(Table1[Customer]),[Sum of Sales])
We have to use the ALL function as the rank needs to be aware of ALL Customers rather than just the current Customer.
Note that we use [Sum of Sales] as we can’t use SUM(Sales) in this case as there is no row context available – we would need to wrap SUM(Value) into a CALCULATE function. As it happens the query engine does this for us automatically hence there is no need for us to bother.
The next measure to create is to show the Standard Deviation between the different sales.
In this example I pleased the other fields into the slicer area of the Pivot Table as the focus in this case is Customer.
The sample workbook is available to download from here.
In the prior version of Power Pivot the workaround to Rank was not easy. Thankfully, Microsoft have seen sense and built Rank into the latest release.
I hope you’ve enjoyed this post. As always, feel free to comment.