Post
Ask Question

Calculate Cumulative/Running Total In Power BI

Overview

In this article, we will learn how we can calculate the cumulative total within Power BI. There are some practical scenarios where we need to find out running total with some predefined formula.

Cumulative Total Definition

The cumulative total is also known as Running total. It is the sum of a sequence of numbers, which is updated each time a new number is added to the sequence. In simple words, we can say it represents the current sequence + its previous rows sequence.

Scenario

I have the following table structure. I have 3 columns.

  • Date
  • Loan Increasing
  • Loan decreasing

I want to calculate Cumulative Loan Balance.
 
Loan Balance = Loan Increasing – Loan decreasing

Now, in the end, I need a result for Cumulative Loan Balance something like the following screenshot.

Example: For the month of March, the Cumulative Loan Balance should be like,

  • Loan Increasing – Loan Decreasing + Previous month Loan Balance.
  • So, it would be like = 900-700+900 = 1100

Download Sample Code Files from GitHub.

For your practice, I uploaded the sample Power Bi file and data file on GitHub. You can directly download the code files using the below URL.

 

 

Practical Solution with DAX

Here, we need to find out the Cumulative Total.

First, let’s find out the normal Loan Balance which is the difference between the Increasing Loan Balance and Decreasing Loan Balance.

Step 1

Create a new measure with following formula,

 
Loan Balance = SUM(Sheet1[Loan Increasing ])-SUM(Sheet1[Loan Decreasing])

Below is an output for the Loan Balance.

 

Step 2

Now, let’s calculate its Cumulative Total.

To find out the Cumulative Total, we will use the following DAX expressions.

  • Calculate()
  • Filter()
  • ALL()
  • MAX()

By combining all above DAX expression, create the following formula.

  1. Cumulative Loan Balance =      
  2. CALCULATE (      
  3.    [Loan Balance],      
  4.    FILTER (      
  5.       ALL ( Sheet1[Date ].[Date] ),      
  6.       Sheet1[Date ].[Date] <= MAX ( Sheet1[Date ].[Date] )  
  7.    )    
  8. )   

Here, calculate function will calculate the cumulative Loan balance by applying the date filter by month. So, we can get the desire result set.

Please find below screenshot which represents the Cumulative Loan Balance.

Conclusion

This is how we can easily calculate Cumulative Total within Power BI. I hope you love this article! Stay connected with me for more amazing articles in the future.


Similar Articles