Sign in or start a free trial to avail of this feature.
9. The RankX Function
Ranking things is a very common business task. Learn how to do it in this lesson using the RankX function.
Lesson Goal (00:18)
The goal of this lesson is to create a measure that ranks products in order of total sales using the RankX function.
Creating an Initial Table (00:23)
One method of ranking items is to create a table visualization, and sort it by the relevant field. For example, we create a table ranking products by their total sales. This makes it easy to identify the top few products, but it’s not easy to numerically rank the middle or lower products at a glance. To do this, we want to create a ranking measure.
Creating a Ranking Measure (01:09)
We can create a ranking measure using the RANKX function. It’s best to use RANKX in a measure, as rankings should usually be dynamic and responsive.
RANKX takes two principal arguments. The first is the table containing the items to be ranked. In our example, we use the ALL function on the product name column to indicate that we want to rank the products in our data set. The second argument is the expression used to rank the items in the table. In our example, this is the total sales measure.
As a rule, you should use a measure as the expression to calculate, instead of entering a formula. Your ranking measure may return unexpected results if you enter a formula as the ranking expression.
When RankX is Useful (03:17)
Ranking measures are useful when you want to compare discrete items, like products or salespeople, and you are less interested in the underlying figures for sales, profits etc. Ranks are particularly useful for identifying the performance of middle and lower ranked items that cannot be easily identified by sorting.
When comparing 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 to each other. In this lesson, we'll create a measure that ranks products in order of total sales using the RankX function. We'll start by creating a table of product name and sales.
We'll center the table and resize it. We'll also navigate to the format section of the visualizations pane, select grid, and change the text size to 12.
We can easily identify the top ranked products by simply sorting the table by sales. We'll select the sales column header to sort values from large to small. However, even now, it's not easy to identify a numeric rank through the middle or lower products at a glance. We can create these ranks in DAX using the RankX function.
You almost always want rankings to be dynamic and responsive to any filters and slicers, so we'll create the rank as a measure.
We'll navigate to the modeling tab and select new measure.
We'll call the measure "Sales rank," and enter the RankX formula.
RankX takes two arguments. The first is the table housing the data that we want to evaluate.
In this case, we want to evaluate all the products in the Pharma data table. As such, we'll enter the "all" function, then product name field, followed by a closing bracket and a comma.
We'll cover the "all" function in greater detail in the next course, but for now, just know that it's a filtering function that we'll use to evaluate an entire table. In this case, we'll use "all" to compare sales for each product to all the products in the data set.
The next argument is the expression we want to rank.
We want to rank the products by their total sales, so we'll take "sum of sales." We'll then press enter to create the column. Let's add the measure to the table.
Something clearly isn't working right. All the products have a rank of one.
Let's select the measure in the fields list to highlight the formula.
The issue is that this "sum of sales" function is actually computing total sales for the entire data set instead of sales for one specific product.
We can fix this by deleting the "sum of sales" and replacing it the total sales measure we created in an earlier lesson.
We'll change the formula and press enter to accept the new measure.
Although the total sales measure is itself simply the sum of sales, 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 are due to evaluation contexts. We'll learn more about the details behind these contexts in the next course, but for now, the best advice is to use measures as the expression to be ranked in the RankX formula. As you can imagine, ranks can be particularly useful to a manager or client who is interested in comparing products, salespeople, or other discrete items and less interested in the specific numbers. Ranks are probably more useful when you want to find middle or lower-ranking products that cannot be easily identified by sorting. You generally want to accompany a rank with some sort of numeric field to make sure that you can get both relative and numeric comparisons between products. In the next lesson, we'll continue our series on various DAX functions and look at both Count and Distinctcount.