Sign in or start a free trial to avail of this feature.
9. Ranking Data
Ranking things is a very common business task. Learn how to do it in this lesson using the RankX function.
Rankings in DAX
- The RankX function is used in DAX to create rankings
- Ranks allow you to easily compare products, sales people or anything else that you want to evaluate the performance of
The RankX function
RankX has two required arguments:
- First is the table or column of things to be ranked
- Here we use the all function to make clear that we finding each products ranking among the entire set of products
- Second is the expression that is the basis for the ranking
- It is generally best to use a measure for this argument, as you may get unexpected results otherwise
- Alt B, F – Create new measure
When comparing discrete fields, such as products, we often want to compute a rank. Ranking can be particularly useful if your manager or client is more interested in tables and numbers than graphs but you still want to easily compare products. In this lesson, we'll look at how to create a rank with the RANKX function.
Here we have a simple pivot table of product name and sales.
We can identify the top or bottom ranked products very easily by right clicking on a sales figure and selecting sort from largest to smallest.
However, this doesn't make it easy to identify a numeric rank for the middle or lower products at a glance.
Instead, we'll create a measure that ranks products by their total sales.
We'll create a new measure, call it Sales Rank, and enter the RANKX formula.
RANKX takes two arguments.
The first is a table that contains the entries that we want to rank. In this case, we want to rank all of the products in the Pharma Data table.
We'll therefore apply the ALL function to the product names column in the Pharma Data table.
ALL is a filtering function, which we'll use to indicate that we want each product to be compared to all other products, ignoring any applied filters.
The next argument is the expression we want to use to determine the rankings.
In this case, we want to rank each product based on the sum of sales.
We'll use the SUM function on sales.
Press OK to add the measure to the table.
Then resort by sum of sales from largest to smallest. Clearly, something isn't right.
All products have a ranking of one.
We'll enter the shortcut ALT+B+F.
Go to Manage Measures, select Sales Rank, and edit to edit the measure.
Although we can't see it, the issue here is that the SUM function is actually computing total sales for the entire data set at every row instead of sales for one specific product.
We can fix this by deleting the sum of sales and instead using the total sales measure we created in an earlier lesson.
We'll then press OK and close the Manage Measures window.
Although the total sales measure is simply the sum of sales with no reference to products, we can see that our ranking is now working exactly as we would expect.
The reason we see different results, for seemingly the same calculation, is due to a fairly complex concept known as the filter context.
We'll learn more about the filter context in the next course. But for now, the best advice is to use measures as the expression in the RANKX formula in order to produce the results you expect.
Ranks can be particularly useful to a manager or client who is more interested in comparing products, sales people or other discrete things, and is less interested in specific numbers for sales or revenues.
Ranks are probably more useful for finding middle or lower ranking products that cannot easily be identified by sorting.
Ideally, you should accompany a rank with some sort of numeric field to ensure that you can get both relative and numeric comparisons between products.