Sign in or start a free trial to avail of this feature.
7. Rankings over Time
In previous courses, we have created ranks of a single field at a time. Here, we will create a rank of salespeople for every month in the year, with a single measure.
Ranking using the RANKX function
- The RankX function can be used to create ranking tables, with no additional complexity over the simpler tables we saw in previous courses
- Here we create a table tracking the rank of each salesperson by revenue generated for each month in the year
- Ranking tables can be excellent tools for generating business insights; for instance, we see here that there is no clearcut best salesperson for the entire year
- Alt B, F – Create new measure
- Alt N, V – Create a PivotTable
In the previous lesson, we looked at using a chart of revenue over time, to compare the performance of salespeople. In this lesson, we'll create a table that tracks a ranking of salespeople over time. In our earlier DAX courses, we created some simple rankings based on values at one point in time.
Here, we'll look at a more complicated example by tracking rankings over multiple points in time. Through this lesson, we'll see that the measures we created to track rankings over time are no more difficult than the simple ranking measures we created in previous courses. Before we create our ranking measure, we'll create a measure for the field we want to rank our salespeople on.
We'll rank them on total revenue generated.
We'll create a new measure, call it, total revenue and enter some of revenue as the formula.
We could've easily written this into our ranking formula, however you may remember that ranking by a measure avoids any unexpected issues with filter contexts later on.
We're now ready to create the ranking measure.
We'll create another new measure, call it revenue rank, and enter the rank X formula.
We want to rank salespeople, so the first argument here is an all function applied to the salesperson column.
The expression to rank, is the total revenue measure we just created.
We'll close the formula and select okay to create the measure.
We'll now create a pivot table, that tracks rankings over time.
We'll create the table, add it to a new sheet, navigate to the sales table, and add salesperson to the rows area.
Our data is collected daily, so it makes more sense to use the date hierarchy to aggregate the rankings by month. Therefore, we'll navigate to the dates table, and add the date hierarchy to the columns area.
Finally, we'll add revenue rank from the sales table, to the values area.
At this point, we encounter a small problem. Our date table runs from 2010 to 2019, so we have rankings from all those years, even though our sales data is only collected in 2016.
We'll deal with this by introducing a filter. We'll navigate to the dates table, and add year to the filter area.
We'll then select the year to filter, and select 2016 only.
In order to see monthly rankings, we'll simply select the plus icon by 2016.
Although this table may look like a confusing set of numbers, there are actually many insights we can derive from it. For example, we can track the performance of an individual salesperson by reading across their row.
In the previous lesson, we saw that Holmberg generated the most overall revenue.
Looking at the relevant row, we can that Holmberg ranked highly in most months, but ranked quite low in July and November.
There are many possible explanations for this.
Maybe Holmberg was on leave in those months.
Or, perhaps the ranking of salespeople in any given month is dependent on large transactions.
This could explain the volatility in rankings, evident in this table. Either way, this table could be used to investigate sales patterns more thoroughly. The most obvious limitation of a table like this, is that it only evaluates salespeople in comparison to each other. The table does not tell us how much revenue is generated by a specific salesperson or the company as a whole.
However, if you already have access to those figures and are just interested in comparing salespeople, a table like this can be very useful. Let's stop the lesson here. In the next lesson, we'll return to a more numeric analysis and look at how to create charts that who is above or below average.