6. Running Totals with Varied Starting Points

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

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

Summary

  1. Lesson Goal (00:34)

    The goal of this lesson is to create a chart that calculates a running total starting from a sales person’s first transaction.

  2. Calculating Days Since a Salesperson’s First Transaction (00:42)

    A standard running total chart works well when all the lines on the chart start at the same date. Sometimes this may not be the case. For example, sales people may join the company at different dates during the year. This can make it difficult to gauge the relative performance of sales people starting at different dates. We can deal with this issue by creating a running total chart that starts from a sales person’s first transaction.

    Our aim is to create a new column in the sales data set. For each transaction, we will calculate the days since the first transaction for the relevant sales person. We find the date of the sales person’s first transaction using CALCULATE. The expression we calculate is the minimum value from the date column. The filter we apply uses the ALLEXCEPT function to remove all filters from the sales data except the sales person filter, provided by the row context. This means we find the earliest date for the relevant sales person. We subtract this date from the date of the current transaction to find the result we are looking for. After creating the column, we may need to ensure that it is formatted as a number, not a date.

  3. Viewing and Modifying the Column (02:36)

    In order to study the days since a sales person’s first transaction, we create a Pivot Table showing the dates of every transaction for each sales person, and the corresponding days since the sales person’s first transaction. We find that the summarization in the Pivot Table needs to be changed from Sum to Average. This accounts for situations where a sales person may have more than one transaction on a particular day.

  4. Creating a Running Total (04:07)

    Our next step is to create a running total measure based on the days since first transaction column in the sales table. As this measure is very similar to the previous running total measure we created, we simply edit the first running total measure, replacing the date field with the days since first transaction field.

  5. Creating a PivotChart of the Data (05:15)

    After creating our running total measure, we can add it to a Pivot Chart, which shows us a running total of revenue for each sales person. Each sales person has a separate line, showing their running total of revenue since the date of their first transaction.

    This type of chart can be insightful. For example, it lets us see at a glance which sales people have generated high or low revenues, and how those revenues have grown over time. However, it also has a few drawbacks. In our case, a high number of lines make it difficult to track individual sales people. Also, we need to be careful of possible seasonal variance in sales. A sales person with low initial sales may have just started at a quiet time of the year.

Transcript

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.

Excel Excel for Business Analytics Learning Plan
DAX in Power Pivot
Practical Applications of DAX

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial