1. Calculating Running Totals

 
Subtitles Enabled

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

Free trial

Practical Applications of DAX

9 lessons , 3 exercises

Preview Course

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.

Summary

  1. Course Overview (00:10)

    The purpose of this course is to demonstrate how DAX can be used in business situations to create visualizations and understand a company’s performance.

    In this course, we use a 2016 sales data set for a software company. Each row of the data set represents a sale to a customer. Each row represents a sales transaction and each customer appears only once in the data set. The data contains various columns of relevant information.

  2. Lesson Goal (01:22)

    The goal of this lesson is to create running totals for sales in each region.

  3. Creating a Running Total (01:32)

    At a given date, a running total tracks the overall sales from the start of the data set to that date. Studying running totals lets us see how revenue grows over time. It can be a useful metric when the underlying data is collected too frequently for us to analyze it easily.

    Creating a running total in Power BI is easy, as we can use a Quick Measure to create the DAX formula. When creating the Quick Measure, we specify the field we want to calculate the total for. We specify the field to calculate the total over, which will usually be a date field.
    In our case, we create a running total of revenue over time, and create a line chart of this measure by region. This shows us which regions contribute the most revenue, and how the revenue in each region grows over time.

  4. Modifying and Interpreting the Chart (03:18)

    After creating the measure, we can analyze it in many different ways. For example, we can adjust the legend field of the line chart. This allows us to analyze the running total for revenue by sales person, by state, by region, or by any other field we are interested in. Some of these charts will be easier to understand than others. These charts can be used to obtain various insights about the company’s revenue growth over time.

Transcript

In the previous two courses, we've introduced many important principles of the DAX formula language.

In this final course on DAX, we'll apply many of these principles and look at several examples of DAX in action. Our focus in this course is to demonstrate the use of DAX in business situations.

Through these examples, we'll create more complex charts and visualizations than we've seen so far. We'll also discuss how these charts and formulas can uncover new insights into a company's performance.

We'll use the same sales data set that we've used in other courses on visualizations.

This data focuses on sales for a software company in the United States.

There are 1,000 rows of data relating to sales in 2016.

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

Each entry includes the date of the transaction, the revenue, the quantity of sales, various geographical information about the company, and the product name.

Let's move over to Power BI. Note that we've already loaded this dataset and a date table and have created a relationship between the tables.

In this first lesson, we'll create running totals for sales in each region.

Running totals can be a useful way of tracking performance trends over time.

Let's start by creating a line chart of revenue by region.

We'll navigate to the Visualizations pane, select a line chart, and expand it to cover most of the canvas.

We'll then navigate to the Sales table, add the date field to the Axis well, add the revenue to the Values well, and add region as a legend.

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

Our data is collected almost every day, which means line has many points, and there's too much volatility for us to read the chart. It would be better to create a chart of the running total of revenue.

Creating a running total is actually quite easy, as running totals are available through the quick measures feature. Let's create a quick measure by navigating to the home tab, and selecting New Quick Measure.

We'll scroll through the list of calculations, and select Running Total.

The base value is the value we want to calculate. In this case, revenue.

The field box is a field to calculate the total over.

In this case, the date field from the sales table.

We'll leave the direction as ascending, and press okay to create the new measure.

We'll now select the line chart and remove revenue from the Values well.

We'll replace it with the new running total measure.

We can see that the chart now makes a lot more sense than before, as we can very easily see the revenue trends for each region over the length of the dataset.

Let's change the chart by adding a different field for the legend area.

If we take out Region and add State, we can see there are probably too many lines to read the chart easily. If we replace State with Salesperson, we can see the sales trend for each individual salesperson.

Let's go back to region for now.

There are a lot of insights we could draw from a chart like this. For example, we can see that northeast and south regions generate the most revenue.

The gap is not particularly large, suggesting the company should treat both regions as equally important.

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

This suggests there is no seasonal trend of sales, and revenue is generated at a fairly constant rate throughout the year. As we can see, running totals offer a useful way of visualizing data over time. In the next lesson, we'll look at calculating market shares.

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