Sign in or start a free trial to avail of this feature.
1. Calculating Running Totals
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.
- At a given date, the running total tracks the revenue for all dates up to an including that date
- The running total lets us see how the revenue grows over time
The Running Total Quick Measure
- Running Totals can be calculated as a quick measure in Power BI Desktop
- The Date field used in the calculations should not be a date hierarchy
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.