Sign in or start a free trial to avail of this feature.
6. Normalizing Running Totals
Creating a running total chart can be difficult if each line starts at a different point in time. In this lesson, we create a running total that shows revenue for each salesperson since the date of their first transaction
Running Totals with Different Start Points
- Different lines on previous running total charts can only be compared when each line starts at the same point in time
- This may not be the case for fields like Sales People, as each salesperson may start selling on a different day
- The running total charts we saw previously will be less effective in this situation
Creating a chart of revenue since the first transaction
- To solve the problem, we create a column in the transaction table, representing the days since the first transaction date for the relevant salesperson
- We then create a running total chart using this new column instead of a date column
- This chart lets us see the total amount of revenue each salesperson generates in a particular number of days from their first transaction
- Salespeople who join the company and start selling at different times can be compared more effectively using this chart
- However, we need to be aware of issues that may be affected by different start dates, such as seasonality
In an earlier lesson, we saw how to create a running total of revenue by region.
This provided useful insight into revenue growth over time across various regions, however, it may not work as well for other fields in our data model. The X-Axis for this chart measures time, so the lines can only be compared if the sales period to each region beings at the start of the year. This is clearly the case here, but this assumption may not hold for other fields. For example, this chart would be less effective for the Sales People field as new sales people may join the company in the middle of the year. In this lesson we'll address this issue by creating a chart that calculates running total starting from the sales person's first transaction. As a first step we'll need to go to Data View and look at the 2016 Sales table, our strategy is to create a new column in this table that calculates the number of days since the first transaction of the relevant sales person at every row.
We'll then use this column to create our new running total.
We'll create a new column called Days Since First Transaction.
This column will subtract the current transaction date from the first transaction date for the relative sales person at every row.
Let's initially focus on the second part of this formula. To find the first transaction date, we'll use a CALCULATE function.
The expression for this CALCULATE is the minimum of the Date column, or the date of the first transaction. We want the filter condition to show the entire table for the current sales person and remove all filters from the other columns.
Therefore, we'll use the ALLEXCEPT function.
The two arguments here are the Sales table, followed by the Sales Person column.
This removes all the filters from the table except the Sales Person filter which is provided by the row context.
At this point, we can simply add the Date column from the Sales table to the beginning of the formula and subtract the CALCULATE function we just created.
We'll now press enter to create the new column.
This column is formatted as a date, so we'll go to the Modeling tab and change the data type to Whole Number.
We'll now head back to Report view, navigate to the Days Between Transactions page and create a table, we'll add Sales Person, Date, and the new Days Since First Transaction column.
We'll then navigate to the Formatting tab, increase the text size to 12 and expand the table to cover the left half of the canvas.
Let's look at information for the sales person Arellano.
Their first sale was on January first, and at a glance the new column seems to correctly measure the number of days since that date. However, there's clearly a problem at March 13th.
The value of 144 appears too high, it turns out Arellano had two transactions on this date and the column has added those two values together.
To fix this we'll navigate to the Fields area of the Visualizations pane, choose the arrow next to Days Since First Transaction and change the aggregation from Sum to Don't Summarize.
Let's now create a running total Measure from data in this new column.
As we saw before, running totals are available as a Quick Measure so we'll navigate to the Home tab and select New Quick Measure, we'll then select running total as the calculation, set the base value as revenue and the field to Days Since First Transaction.
We'll then press Okay to create the Measure.
At this point, we'll select a Line Chart from the Visualizations pane and expand it to cover the right half of the canvas.
We'll add Days Since First Transaction to the Axis Well, the new Running Total Measure to the Values Well and Sales Person to the legend well.
Note that this chart is similar to the running total chart that we previously created, however the X-Axis now displays the number of days since each sales person's first transaction.
This chart is particularly useful for analyzing the performance of sales people.
For example, he top line shows us that the sales person Holmberg has consistently been a top performer with many large transactions.
If we look at the sales person Moran, we can see they had fewer large transactions early on, but have recently had several large sales, resulting in a revenue spike.
However, this chart has it's limitations.
For example, we cannot determine the corresponding date from each sale from this chart alone.
This could be misleading if a business has seasonal sales patterns as a sales person with low initial sales could have joined during a quiet time of the year.
Through this lesson we've looked at just one of the many possible applications for a running total chart like this.
If we add a data set of products, a running total chart like this could be very useful in tracking the product lifecycle. In the next lesson, we'll continue looking at how DAX can help us analyze values that change over time, with a focus on rankings.