Power BI Running Total

What is Running Total in Power BI?

Running Total, also known as Cumulative Total or Running Sum, is a calculation that shows the accumulated sum of a measure or a column in Power BI. It is commonly used to track the incremental growth or cumulative value of a measure over a specific dimension or period. Power BI Running Total provides two key advantages over other sum functions.

  • It performs the sum of the numbers without the need for manual summing of the entire sequence every time a new sequence is added to the data.
  • It can record the sequence itself, eliminating any need for manual sequence saving.

Syntax

To create a Power BI running total measure, use the DAX (Data Analysis Expressions) function called TOTALYTD or write a custom formula using the CALCULATE and SUM functions.

Here’s an example of the syntax for both methods:

  • Using CALCULATE and SUM functions:

Running Total Measure = CALCULATE(SUM(‘Table'[Value]), FILTER(ALL(‘Date’), ‘Date'[Date] <= MAX(‘Date'[Date])))

Power BI Running Total - Calculate
  • Using TOTALYTD function:

Running Total Measure = TOTALYTD(Expression, Dates, [Filter], [YearEndDate])

Power BI Running Total - TOTALYTD

These two functions will calculate the Power BI Running Total by date in a report. Similarly, you can use other DAX functions such as TOTALQTDTOTALMTD to calculate the Power BI Running Total by Quarter and Power BI Running Total by month.

Key Takeaways
  • Power BI Running Total is a calculation that shows the accumulated sum of a measure or column.
  • You can create a running total using the TOTALYTD, TOTALMTD, and TOTALQTD functions or by writing a custom formula with CALCULATE and SUM functions.
  • Running totals are useful for tracking cumulative values or incremental growth over time or a specific dimension.
  • Pay attention to data ordering, performance considerations, and the dimension/column used in the running total calculation to ensure accurate results.

How to Create Running Total in Power BI?

To create a running total in Power BI, you can follow these steps:

Step 1: Open Power BI Desktop and load your data into the model.

Power BI Running Total - Create - Step 1

Step 2: Navigate to the Fields pane and select the table that contains the column or measures you want to create a running total for.

Power BI Running Total - Create - Step 2

Step 3: Go to the Modeling tab in the Power BI ribbon. Click on the New measure button to create a new measure.

Power BI Running Total - Create - Step 3

Step 4: Enter the appropriate DAX formula in the formula bar using either the TOTALYTD function or the CALCULATE and SUM functions, as mentioned in the syntax examples above.

Power BI Running Total - Create - Step 4

Step 5: Give the measure a meaningful name, such as Running Total or Cumulative Sum. Apply the measure to a visual or use it as needed in your report or dashboard.

Power BI Running Total - Create - Step 5

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples of Running Total in Power BI

In this section, we will see 2 examples where we will provide a step-by-step process to create a Power BI Running Total measure and then use it in the reports.

Example #1

In this example, we will calculate the Power BI Running Total by Category using the CALCULATE and SUM DAX functions. We have sourced a US Superstore dataset containing Sales data across various product segments across multiple regions to Power BI. To create the Power BI Running Total, follow these steps:

Step 1: Open the Power BI Desktop and import the US_Superstore_Dataset using Data Connection. You can also customize the dataset as per requirement by using the Transform Data option.

Power BI Running Total - Example 1 - Step 1

Once imported into Power BI, you will be able to view the data fields in the Fields pane.

Step 2: Navigate to the Fields pane, right-click on the Orders table, and choose the New measure option.

Power BI Running Total - Example 1 - Step 2

Once you choose the New measure option, you will see a prompt in the formula bar.

Step 3: Enter the DAX expressions in the formula bar to create the Running Total Sales measure.

The DAX we have used here includes Power BI CALCULATE, SUM, and FILTER functions. The FILTER function returns all the dates in the Orders table. Similarly, SUM and CALCULATE functions will perform a cumulative total sales for the loaded dataset.

Power BI Running Total - Example 1 - Step 3

Once you click on the Commit button, the Running Total Sales measure will be created in the Orders table.

Step 4: Navigate to the Fields pane, then drag and drop the fields into the report canvas and finally, select the Table visual from the Visualization pane.

Power BI Running Total - Example 1 - Step 4

Step 5: Navigate to the Visualization pane and choose Slicer Visual. Navigate to Format your visual to apply any formatting options for the Slicer.

Power BI Running Total - Example 1 - Step 5

As you can see in this Power BI report, this visual highlights Total sales and Running Total Sales per category and segment. The Power BI Running total by category is a cumulative sum of total sales per category and segment across the dataset. You can also add additional fields to perform the calculations at different levels of granularity.

Example #2

In this example, we will calculate the Power BI Running Total by date and Power BI Running Total by month measures using the TOTALYTD and TOTALMTD DAX functions. We will use the same US Superstore dataset that we have used in the 1st example to demonstrate the cumulative sum workings in Power BI. To create the Power BI Running Total, follow these steps:

Step 1: Open the Power BI Desktop and import the US_Superstore_Dataset file using Data Connection.

Power BI Running Total - Example 1 - Step 1

Once imported to Power BI successfully, you can see the data attributes in the Fields pane.

Power BI Running Total - Create - Step 2

Step 2: Navigate to the Fields pane, right-click on the Orders table, and choose the New measure option.

Power BI Running Total - Example 1 - Step 2

Step 3: Enter the DAX expressions in the formula bar to create a new measure. Here we have created two new measures, i.e., RT TOTALMTD and RT TOTALYTD.

RT TOTAL MTD calculates the Power BI Running Total by Month for sales data.

Example 2 - Step 3

RT TOTALYTD calculates the Power BI Running Total Year to Date sales number.

Example 2 - Step 3 - RT TOTALYTD

Once you have entered the Power BI DAX, click on the Commit icon to save the changes. It will create the new measures in the Orders table.

Step 4: Navigate to the Fields pane and drag and drop the appropriate data fields into the report canvas. Select the Table visual from the Visualization pane.

Example 2 - Step 4

Step 5: Navigate to the Visualization pane and choose Slicer Visual.

Example 2 - Step 5

Navigate to Format your visual to apply any formatting options for the Slicer.

Here we have chosen the Slicer Style as a Dropdown and Select all option in the dropdown value.

Example 2 - Step 5 - Slicer settings

It will create a Power BI report with the Power BI Running Total measure embedded in the report.

Example 2 - Step 5 - Slicer

You can apply Power BI filters on different regions, and it will perform running totals on the filtered dataset accordingly.

Important Things to Note

  • Running totals are calculated based on a specific dimension or time column. Select the appropriate column in your formula to ensure accurate results.
  • Running totals can be impacted by the data ordering or sorting in visuals. Ensure that the data is sorted correctly to display the desired running total.
  • Running totals can consume memory and processing power, especially with large datasets. Use them judiciously and consider the performance implications.
  • Make sure you refresh your datasets to ensure the new data is taken into account for Running Totals calculations and reporting.
  • If you are using any visualization to represent the Running Total calculation, ensure you pick the right visuals for accurate data reporting and enhanced user experience.

Frequently Asked Questions (FAQs)

1. Why is Power BI Running Total not working?

There could be several reasons why a Power BI running total may not work as expected. Here are a few possible issues and solutions to consider:

Incorrect column or measure selected: Double-check that you have selected the correct column or measure in your running total calculation.
Incorrect date or dimension column: If you are calculating a running total over time or a specific dimension, ensure that the date or dimension column you are using is correct.
Data sorting: Running totals are sensitive to the data sorting order. The running total may produce unexpected results if your data is not sorted correctly.
Incorrect formula syntax: Check the syntax of your running total formula to ensure it is accurate. Pay attention to parentheses, commas, and function names.

2. What are the types of Running Total in Power BI?

There are mainly two types of running totals in Power BI:

Running Total within a Group or Dimension: This type of running total calculates the accumulated sum within a specific group or dimension. For example, you may want to calculate the running total of sales for each product category or customer group.
Running Total over Time: This type of running total calculates the cumulative sum of a measure over a specific period. It is commonly used to track metrics like revenue or profit over time, such as monthly or quarterly running totals.

3. What is the difference between a Running Total and a Cumulative Total in power BI?

In Power BI, the terms Running Total and Cumulative Total are often used interchangeably, as they refer to the same concept. Both represent the accumulated sum of a measure or column.
Running Total generally refers to the ongoing accumulation of values, where each value is added to the previous total. It can be used within a specific group or dimension or over time.
The term Cumulative Total emphasizes the cumulative nature of the calculation. It signifies that each value in the series contributes to the total, resulting in a cumulative sum.

Download Template

This article must be helpful to understand the Power BI Running Total, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to Power BI Running Total. We learn the step-by-step process to create and use running total measure in reports with examples. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *