1. Running Totals

Overview

Running Totals let you track the growth pattern of a field over time. In this lesson, we will see how to create a running total using DAX.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

  1. Lesson Goal (00:24)

    The goal of this lesson is to learn how to calculate running totals in DAX. Running totals can be used to track performance trends over time.

  2. Understanding the Data Set (00:34)

    The dataset focuses on sales for a US-based software company in 2016. Each row represents a sale to a single customer. Each customer appears only once in the data set. Each row contains the customer’s name and geographical details, the revenue amount of the sale and the number of users of the product in the company, as well as the date of the sale and the sales person who completed the sale.

  3. Charting Revenue Over Time (01:25)

    Initially we create a Pivot Chart showing revenue by date. We create a line chart showing a separate line for each region. In our data set, data is collected almost every day. As a result, a line chart is not very effective, as the lines have too much volatility and fluctuation from day to day. When this is the case, it is better to use a running total chart, which smooths out this volatility to show overall trends over time.

  4. Creating a Running Total Measure (02:19)

    A running total of sales calculates the total sales for every day up to the current date. In Power Pivot, we can calculate the running total by creating a measure. The measure uses a CALCULATE function, and calculates the sum of the revenue column for a filtered set of dates representing all days up to the current date.

    The filter is the most complex part of the measure. We use the FILTER function to filter the date column. To find all dates up to and including the current date, we use the function ISONORAFTER. This takes three arguments. First is the date column of interest. Second is the value of interest. We use the MAX function on the Date column as this will return the current date at any point on a Pivot Chart. Third is the sort order, which can be ascending or descending. If we specify ascending, the function finds all dates on or after the current date. If we specify descending, the function finds all dates on or before the current date. As a result, we specify descending in our measure.

  5. Using the Running Total in a Chart (04:18)

    After creating the running total measure, we can simply add it to a PivotChart to view the running total of revenue over time. In our case, the lines are not continuous, because not every region records a sale every day. We can deal with this issue by selecting the chart, then opening the Select Data window from the Design tab of the ribbon. In this window, we select the Hidden and Empty Cells option, then Connect data points with line. This will draw a line between all the points on the line chart.

    A running total chart can be used to generate many insights. For example, our chart tells us which regions generate the most revenue for the company. The shape of the line can also tell us about any seasonal trends in the data. In our case, the lines have a relatively constant slope, indicating that sales are relatively constant throughout the year.

Transcript

In previous courses, we've introduced the principles of the DAX formula language used in Power Pivot. In this course, we'll apply many of these principles and look at several examples of DAX in action.

Our focus in this course is to look at common business situations where you can use DAX formulas to create complex tables and charts.

Our goal in this first lesson is to look at how to calculate running totals for a business. Running totals can be a useful way of tracking performance trends over time.

Before we jump into the meat of the lesson, let's look at our data set. Throughout this course, we'll use a data set that focuses on the sales of a software company in the United States. There are 1,000 rows relating to sales in 2016.

Each row represents a transaction for an individual customer, and each customer only appears once.

Each row details the number of users in the client company, the revenue, various geographical information about the company, the date of the transaction, and the salesperson who completed the transaction.

Let's move to Diagram View in Power Pivot. Note that we have loaded this data set as well as a Dates table to our data model. We've also created a relationship between these tables. Let's start the lesson by creating a line chart of revenue by region.

We'll create a PivotChart and place it on Sheet 1.

We'll then expand the Sales table on the right, add Date as an axis, Revenue as the values, and Region as a legend. We'll then remove the Date Month field and change the chart type to a line chart.

It's immediately clear that this chart is too crowded to be of any use.

Our data is collected almost every day, meaning each line has many points, and there is too much volatility for us to effectively read the chart.

Instead, we'll create a chart of the running total of revenue.

We'll first create a measure to calculate the running total. We'll use the keyboard shortcut Alt + B + F, choose New Measure, and name it Running Total Revenue.

We want this measure to calculate all sales at any given date, up to and including that date.

We can do this using a CALCULATE function.

The expression of CALCULATE will just be the sum of revenue.

We must now define the filter for the CALCULATE.

We'll enter the FILTER function, followed by an ALL function, applied to the Date column from the Sales table.

However, we want the FILTER expression to only identify all dates up to the current date.

To do this, we'll use a function called ISONORAFTER.

This takes three arguments.

The first is the Date column we've been using, which, in this case, is the Date column from the Sales table.

The second is the MAX function applied to this Date column.

And the third is a sort order, which will be DESC for descending.

Let's close the formula and take a moment to think about what this function is actually doing. When we add this measure to a PivotChart, the MAX function will always return the current date at any point on the chart.

ISONORAFTER will find all dates that are less than or equal to this date because we specified a descending order.

Finally, the CALCULATE function will sum the revenue for all of these dates. This will become more clear when we add the measure to our chart.

We'll press OK to create the measure, select the line chart, and remove Revenue from the values area.

We'll replace it with a new Running Total measure and see that we get a chart which makes a bit more sense than before.

As mentioned previously, our data is collected daily. However, some regions will not have a sale every day, so there are many gaps in the lines.

We can fix this by navigating to the Design tab and choosing Select Data.

We'll then select Hidden and Empty Cells, Connect data points with line, and press OK twice to accept the changes.

Our chart now displays proper running totals. We can very easily see the revenue trends for each region over the length of the data set.

There are lots of insights that could be drawn from this chart. For example, we can see that the Northeast and South regions generate the most revenue for the company. Note that the gap is not particularly large, suggesting that the company should treat both regions as equally important.

It's also notable that the slope of each line seems to be relatively constant.

This suggests that there is no seasonal trend to sales and revenue is generated at a fairly constant rate throughout the year.

CLearly, running total charts offer a useful way of visualizing data over time in Power Pivot. In the next lesson, we'll look at how to calculate market shares using DAX.

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