Sign in or start a free trial to avail of this feature.
6. Running Totals with Varied Starting Points
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 sales person since the date of their first transaction
Running Totals with Different Start Points
- Measuring running total revenue for each sales person can be difficult, as each sales person 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 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 sales person
- 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 sales person generates in a particular number of days from their first transaction
- Sales people who join the company and start selling at different times can be easily compared using this chart
- F4 – Enter formula bar
- Alt, H, Y2 – Format column as whole number
- Alt H, PT, T – Create PivotTable (from Power Pivot window)
- Alt JT, G – PivotTable Field Settings
- Alt N, SZ, C – Create PivotChart
- Alt JC, C – Change chart type
- Alt JC, E – Select Data
In an earlier lesson, we saw how to create a running total of revenue by region. This provided a useful insight into revenue growth over time, across the various regions, however, it relies on the fact that the company started selling to all regions at the beginning of the year.
For other fields, such as sales people, this assumption may not hold, as sales people may join the company in the middle of the year. In this situation, the standard running totals chart would be less effective.
In this lesson, we'll address this issue by creating a chart that calculates a running total starting from a salesperson's first transaction.
We'll start by going to the data model and looking at the 2016 Sales table.
Our strategy is to create a new column that calculates the number of days since the first transaction of the relevant salesperson at every row.
We'll then use this column to create our new running total.
We'll create the new column and call it Days Since First Transaction.
This column will subtract the value in the Transaction Date field from the first transaction date for the relevant salesperson.
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 function will be the minimum of the Day column or the date of the first transaction. We want the filter condition to show the entire table for the current salesperson and remove all filters from other columns.
To do this, we'll use the All Except function.
The two arguments here are the Sales table followed by the Salesperson column.
This removes all filters from the table except for the Salesperson filter which is provided by the row context.
We'll now finish off the formula by entering the Date field from the Sales table at the beginning and subtracting the calculate.
We'll then press Enter to create the column.
Note that it's currently formatted as a date, so we'll change the data type to a number.
Let's create a pivot table and add it to a new sheet to see this column in action.
We'll navigate to the Sales table, add Salesperson and Date to the Rows area and the new Days Since First Transaction column to the Values area.
We'll remove the Date Month field as we want to look at individual dates.
We can see that the column seems to work as intended.
Let's look at the information for the salesperson, Areiano.
Their first sale was on January 1st and at first 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 to be too high.
Areiano had two transactions on this date and the column has added these two values together. To fix this issue, we'll select this value, go to Field Settings, change Summarize Values By from Sum to Average and select OK.
Let's now create a running total measure from the data in this column.
This new measure will be very similar to our first running total measure, so we'll go to the Manage Measures window, select Running Total Revenue, then Edit and copy the formula.
We'll then press Cancel, create a new measure, call it Running Total From First Transaction, and paste in the formula.
We'll then replace every occurrence of the Day column with the Days Since First Transaction column.
Once this is done, we'll press OK and create the new measure.
We'll select any cell outside the table, add a pivot chart to this sheet, and change the chart to a line chart.
We'll add Days Since First Transaction to the Axis area, the new Running Total measure to the Values area, and Salesperson to the Legend area.
As we did in a previous lesson, we'll go to Select Data, choose Hidden and Empty Cells, Connect Data Point With Line, and select OK, twice.
This chart is very similar to the Running Total chart we previously created, however each Salesperson's line starts at zero, regardless of the date of their first recorded transaction.
As such, it's particularly useful for analyzing the performance of salespeople.
For example, the top line shows us that the salesperson, Holmberg, has consistently been a top performer with many large transactions.
However, the number of lines makes it difficult to track individual salespeople.
Another limitation, is that we cannot see the corresponding date for each sale from this chart alone.
This can be an issue if the business has seasonal patterns, as the salesperson with low initial sales could have joined at a quiet time of the year.
In the next lesson, we'll continue looking at how DAX can help us with how values change over time with a focus on rankings.