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.
Lesson Goal (00:11)
The goal of this lesson is to create a table that tracks a ranking of salespeople over time.
Preparing the Data Set (00:17)
When creating a ranking field, we should always create a measure that calculates the metric which will be the basis of our rank. For example, we want to rank salespeople by their total revenue generated. To do this, we create a measure that calculates the sum of revenue. This measure will then be used in our ranking measure. Creating a separate measure like this avoids possible issues with the filter context later on.
Creating the Sales Rank (01:15)
Ranking measures are created using the RANKX formula. We specify the set of things that we want to rank, and the measure that should be used to rank them. In our case, we rank all our sales people using the total revenue measure.
When we add a ranking measure to a Pivot Table, we can add a date field as well and the rank will be computed for each time period separately. In our case, we create a table containing salespeople in the rows and months in the columns. We add the months using the date hierarchy. The values in the Pivot Table come from the sales rank measure. This creates a Pivot Table which ranks every salesperson in the company for every month of the data set.
Interpreting the Sales Rank (03:06)
A ranking table can be used to generate a variety of possible insights. If we read across the row for a salesperson, we can see the rank of a salesperson each month. This lets us see if an individual salesperson is improving or declining relative to their peers. Reading a column shows us the sales rank for all the salespeople in an individual month.
The main limitation of a table like this is that it does not tell us how much revenue is generated by each sales person, or by the company as a whole. However, a table like this can still be useful to someone who already knows these figures and who is only interested in a comparative analysis.
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.