Sign in or start a free trial to avail of this feature.
1. 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 Measure
- We use Calculate to find the total revenue, with a filter for all dates up to and including the current date
- The function ISONORBEFORE finds all dates that are greater than or equal to a specified date
- However, when specifying a Descending order, ISONORAFTER effectively becomes ISONORBEFORE. Unfortunately, the function name can be confusing in this regard
- Alt H, PT, C – Create PivotChart (in Power Pivot window)
- Alt B, F – Create new measure
- Alt JC, E – Select Data
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.