DIY 3 : Learn to achieve Conditional Running Total in PowerBI using Example

Table of Contents

Microsoft Power BI Desktop Exercise - 3 | Conditional Cumulative  Sum / Running Total

Again while solving the problem on Microsoft PowerBI Community, I got a problem related to the Conditional Cumulative Sum/Running Total so I thought to put that problem along with a solution for my readers here. You can practice it and gain a lot of valuable experience in Power BI.

Well, I am running Microsoft PowerBI DIY Series on the blog, you can subscribe to my blog to get updated content on your mail. Check out the last article below.

Read More:

What Do mean Conditional Cumulative Sum / Running Total in PowerBI? Conditional Cumulative Sum/Running Total means we have to add and subtract the running total next entry based on the basis of particular column value which acts as a condition here.

Problem Statement

When Transaction Type is Buy then we have to do running total/ Cumulative Sum of transaction units and when transaction type is Sell then we have to subtract the transaction units from the cumulative sum taken previously.

Transaction Date Transaction Type Transacted units Stock Name Cumulative figure till date
1.1.2020 Buy 100 A 100
2.1.2020 Buy 50 A 150
5.1.2020 Sell 20 A 130

Microsoft Power BI Desktop Exercise - 3 | Conditional Cumulative  Sum / Running Total

Now let`s see the result table which we want to make –

Microsoft Power BI Desktop Exercise - 3 | Conditional Cumulative  Sum / Running Total

we have to make calculated Column Commulative_Figure_Till_Date.


First, we have to make the Calculated Column with name Commulative_Figure_Till_Date  as shown in the figure above.

Step 1: Make the Calculated Column

To make Calculated Column, Click on the New Column Button in the Home Tab.
Microsoft Power BI Desktop Exercise - 3 | Conditional Cumulative  Sum / Running Total

Step 2: Write DAX Expression for getting the desired result

Please try to write the DAX Expression first on your own and if you are unable to write correct DAX then you can see the DAX expression below and understand the logic used in the Expression.
Commulative_Figure_Till_Date =
VAR TransactionDate = ‘Table'[Transaction Date]
VAR Comm_sum =
        SUM ( ‘Table'[Transacted units] ),
        FILTER (
            ‘Table'[Transaction Date] <= TransactionDate
                && ‘Table'[Transaction Type] = “Buy”
    IF (
        ‘Table'[Transaction Type] = “Buy”,
            – CALCULATE (
                SUM ( ‘Table'[Transacted units] ),
                ‘Table'[Transaction Date] = TransactionDate

Calculated Column creates an Outer Row Context by default so Variable TransactionDate is iterating the value of Transaction Date Column One by One.

FILTER in the Variable Comm_sum is providing inner  Row Context and Transaction Date Column iterate completely so each iteration provided by outer row context of the calculated column so the sum of Transacted units take place of each date less and equal to Current Row Context Date.

Let’s understand the Variable  Comm_sum, Here Sum of Transacted Units is taking place for All dates which are less than and equal to Dates coming from outer row context.

Let’s suppose the outer row context has value 2/1/2020 so the sum of transacted Units will take place for 1/1/2020 and 2/1/2020. But we need to do this only for records which have transaction type as Buy. That’s why we have used AND condition && ‘Table'[Transaction Type] = “Buy”.

AND condition help to do not evaluate the third record because if it evaluates the third record then 150 Cumulative sums will get added to 20 from the third record and would become 170 and then if we use our final IF condition then it will subtract 20  and give us 150 as a result which is incorrect so that`s why  && ‘Table'[Transaction Type] = “Buy” condition has been used in the Code.

IF Condition used in the return statement will do Cumulative Sum for records having transaction type as Buy while for records having transaction type as Sell. 

            – CALCULATE (
                SUM ( ‘Table'[Transacted units] ),
                ‘Table'[Transaction Date] = TransactionDate

the expression is evaluated. Thus Sell Record Transacted  Unit is subtracted from Cumulative Sum coming from previous records.

I know the above lines seem too confusing you but that is the most important element of DAX which we need to understand.

To better understand the Row Context and Filter Context, I highly recommend reading book
“Definitive Guide to DAX”. No book is better than this book.

You can order it from below link –


I hope you find Conditional Cumulative  Sum / Running Total article valuable in your learning Power BI Journey. Go through all the articles and also let me know anything you did not understand in the above article, I will be happy to help you.
Do Share and Like if you find the article useful.
Thank You.

Read More: DIY 2: How to achieve Relative Date filtering in Power BI

Power BI Desktop
Kaushlendra Mishra

How to create a report quickly in Power BI Service?

Do you want to create a quick report in Power BI? The latest Power BI version has made it possible to create a quick report using data from the web and existing published datasets in Power BI Service. So, now you do need to download the Power BI Desktop software into your PC and directly make a report using the Power BI service.

Read More »
Kaushlendra Mishra

What is Availability Set and Availability Zone in Azure?

What is Availability Set and Availability Zone in Azure? In Availability Set, VM`s copy is created and is isolated with separate physical servers, computer racks, storage units, network switches in a data center within a region so if one of the VM stops working then still we have the copy of the VM working within the same datacenter. While Availability Zone ensures that complete datacenter data is replicated to another place at a distance within the same region so that even if complete data center fails due to any reason then also Availability of data is ensured.

Read More »
Kaushlendra Mishra

How to Prepare for DP-200 Exam?

I have passed the DP-200 exam on 28 Oct 2020. I have gone through the various study material and courses in order to prepare for the exam. Overall, the time taken by me for the DP-200 exam is approx 3 months. Ideally, you should not take 3 months of time but due to lack of guidance, I happen to take a number of online courses which was not required to be taken for passing the DP-200 Exam.

Read More »

1 thought on “DIY 3 : Learn to achieve Conditional Running Total in PowerBI using Example”

Leave a Comment

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