# Dynamically Calculate A Power BI Running Total Or Cumulative Total

The Power BI running total is the perfect way to display patterns and changes on a specified data over time.

In Power BI, there is a common combination of DAX functions that allow us to create a dynamic cumulative total (sum) on any report page.

The DAX formula that we’re about to discuss is easy to use and provides dynamic results. You can reuse the same formula combination. Just substitute different core measures or core calculations into it. This will enable you to generate cumulative totals (sums) over different calculations from a wide-scale to daily results.

We’ll be using this Quarterly Insights report that I used during the Enterprise DNA Learning Summit last May 2018 as an example.

## Using The Date Table And Total Sales In Power BI

When running a cumulative total formula, we need to have a strong date table. This also goes for any time intelligence calculations.

Let’s drag these filters from the Quarterly Insights report to the sample report page. This way, we can drill into any time period.

Then, let’s grab the Date field into the sample report page. This will serve as our date table.

Let’s also add the Total Sales column into the sample report page.

The Total Sales is considered as a simple core measure. This is because it’s easy to calculate. It can also be reused in various ways like Moving Averages or Running Totals.

Now, we need to utilize the correct combination of DAX formulas to get the cumulative total (sum).

## Formula For Calculating The Cumulative Revenue

We’ll be using this formula as an example to calculate the Cumulative Revenue for the whole month of July in 2016.

As you can see here, we already have the Cumulative Revenue result that we want. Once we change the context, the cumulative sum also changes. This is what makes it dynamic.

Let’s now try to analyze the given formula.

First, we’ll use the CALCULATE function to change the context of the calculation. Then, we’ll be including the Total Sales measure.

As you can see here, the Total Sales for every single day was displayed.

## Using The FILTER Function For Running Totals

Now, we’re going to use the FILTER function. This will adjust the context inside the CALCULATE function.

If we want to display the proper cumulative total, we need to manipulate the current context. This is for us to calculate not just one day, but all the days within that month as well.

As we go down the list, we need to create a wider time frame that we’re currently accumulating.

Every single row within the Cumulative Revenue column is being evaluated to a different context or filter that has been placed over the Sales table.

## Using The ALLSELECTED Function For Running Totals

Furthermore, the ALLSELECTED function removes any or all the filters from the Date table that are placed within a certain context. In this case, the context is Q3 of 2016.

Then, it reapplies those filters based on this logic.

To summarize, this part removes all filters over a 3-month window. Then, it iterates through every single one of those days to identify whether that date is less than or equal to the current max date.

## Calculating The MAX Date

Let’s try to create a Max Date measure, then assign this logic to it.

This is what the MAX Date logic does.

As you can see, it evaluates to exactly the same day from the Date column.

## Different Ways To Use The Power BI Running Total

Once you understand the logic for calculating running totals, it’ll be easier to use it in different ways.

For example, if we want to calculate the Cumulative Profits, we can still use the formula for the Cumulative Revenue. We just need to alter the formula a little bit.

We need to change the name of the measure to Cumulative Profits. Then, change the Total Sales to another core measure which is Total Profits.

Using this formula, we can also get the cumulative revenue of the last quarter. We’ll name this measure Cumulative Revenue LQ.

In this example, we just need to change the Total Sales to a time intelligence calculation like the Sales LQ.

## Calculating The Revenue Difference Per Quarter

After having the Cumulative Revenue LQ measure, we can now get the difference between the revenue of the current quarter to that of the last quarter. The term for this technique is Measure Branching.

To do that, we need to create a new measure and name it Revenue Diff per Quarter. Then, we can subtract the Cumulative Revenue LQ from the Cumulative Revenue measure.

After successfully integrating the formula to the previously discussed measures (Cumulative Revenue LQ and Revenue Diff per Quarter), we can now display the visualizations for easier data analysis.

## Conclusion

The key point in this tutorial is understanding the formula and then tweaking it further to branch out to other measures.

Using a DAX formula allows you to show trends and provide a concrete comparison of measures over time.

By understanding the function of each section of the formula, you can obtain instantaneous results.

This changes how presentations are done. As long as you’re able to tweak the formula according to the information you require, your desired results will be shown straight away.

This is a good review of the technique for Power BI running total. I hope that you’ll be able to implement this in your own work.

For more DAX formula combination techniques, check out the Solving Analytical Scenarios module at Enterprise DNA Online. This course module covers all formulas that you can use to solve various analysis and insights in your reports.

Enjoy working through this detailed video.

Sam

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, there’s a constant demand for tools that not only help you make sense...

## Understanding Data Models and Visualizations

Power BI is a robust and versatile data visualization tool that has gained popularity for its...

## Getting Started with DAX in Power BI: A Beginner’s Guide

Data analysis expressions (DAX) are the key to unlocking the superpowers of Power BI. If you want to...

## Building a Data Visualization Portfolio – Showcasing Your Skills and Insights

Data visualization is a powerful tool that allows you to communicate complex information in a way that...

## Guide to Intermediate DAX Functions for Power BI

In today’s data-driven world, being able to use data analysis expressions (DAX) in Power BI and other...

## Optimizing DAX: Performance Tips for Power BI Reports

Data analysis in Power BI is not only about creating visually appealing reports but also about ensuring...

## DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.

## Comprehensive Guide to Decision Making Science from Data Insights

As technology advances, decision making science has become increasingly important in the business...

## Introduction to the RANKX and TOPN DAX functions in Power BI

One of the key aspects of analyzing data is to be able to rank or compare different elements based on...

## Introduction to Marketing Analytics for Business

Marketing analytics is a crucial tool for modern businesses. It is a process that helps companies...

## Power BI Visualization Technique: Learn How To Create Background Design Plates

Here's a Power BI visualization technique that you can utilize within your reports by using large...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...