Sign in or start a free trial to avail of this feature.
7. Tracking 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
- We use a measure to create the rank field in order to avoid issues due to the filter context
- 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
In the previous lesson, we used the chart of revenue over time to compare the performance of sales people.
In this lesson, we'll create a table that tracks the ranking of these sales people over the same time period.
As you may remember, we created some simple rankings in our earlier courses on DAX.
Here, we'll complicate things by tracking rankings over multiple points in time.
Through this lesson, we'll see if the measures we create to track ranking over time are no more difficult than the simple ranking measures we previously created.
We want to rank our sales people on total revenue. So let's create a measure that calculates that revenue. We'll create a new measure and call it "total revenue." The formula is simply the sum of revenue.
We could've easily written this into our ranking formula, however you may remember that ranking by a measure avoids any unexpected issues with the filter contexts.
We're now ready to create the ranking measure. We'll again select new measure, call it Revenue Rank, and enter the rank X formula.
We want to rank sales people, so the first argument here is an all function, applied to the sales person column.
The expression "to rank" is the total revenue measure we just created.
We'll press enter to create the measure.
We'll now create a matrix that tracks rankings over time.
We'll navigate to the visualizations pane, select a matrix, and expand it to cover most of the canvas.
We'll then put sales person in the rows well.
Our data is collected daily, but ranking sales people by sales every day would not make sense. Instead, we use the date hierarchy to aggregate the rankings by month.
To that end, we'll add the date field from the dates table to the columns well.
Finally, we'll add revenue rank to the values well.
At this point, we encounter a small problem.
Our date table runs from 2010 to 2019, so we have rankings for all these years, even though our sales data is only collected in 2016.
We'll deal with this by introducing a filter.
We'll scroll down to the filters area and select year.
We'll switch the filter type from advanced to basic, and then select only 2016.
As mentioned previously, we want to view our rankings by month.
Since we only have a single year of data we can ignore the year and quarter levels in the date hierarchy.
We'll select the two arrows in the corner of the chart twice, to show data at the monthly level.
Finally, we'll navigate to the formatting section of the visualizations pane, select grid, and increase the text size to 14.
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 sales person by reading across their row.
In the previous lesson, we saw that Holmberg generated the most revenue overall.
If we look at the relevant row, we can see 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 for those months. Or perhaps the ranking of sales people in a given month is dependent on large transactions.
This explanation could explain the volatility in rankings evident in this table.
Either way, this table could be used to more thoroughly investigate sales patterns.
The most obvious limitation of this table is that it only evaluates sales people in comparison to each other.
It does not tell us how much revenue is generated by a particular sales person, or the company as a whole.
However, if you already have access to those figures and are just interested in comparing sales people, a table like this can be very useful. Let's stop the lesson here.
In the next lesson, we'll look at how to create charts that illustrate who is above or below the average.