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 |

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

we have to make calculated Column

**Commulative_Figure_Till_Date.**

## Solution

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.

### 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 =

CALCULATE (

SUM ( 'Table'[Transacted units] ),

FILTER (

'Table',

'Table'[Transaction Date] <= TransactionDate

&& 'Table'[Transaction Type] = "Buy"

)

)

RETURN

IF (

'Table'[Transaction Type] = "Buy",

Comm_sum,

Comm_sum

- CALCULATE (

SUM ( 'Table'[Transacted units] ),

'Table'[Transaction Date] = TransactionDate

)

)

VAR TransactionDate = 'Table'[Transaction Date]

VAR Comm_sum =

CALCULATE (

SUM ( 'Table'[Transacted units] ),

FILTER (

'Table',

'Table'[Transaction Date] <= TransactionDate

&& 'Table'[Transaction Type] = "Buy"

)

)

RETURN

IF (

'Table'[Transaction Type] = "Buy",

Comm_sum,

Comm_sum

- 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.

Comm_sum

- 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 -

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.

Comm_sum

- 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 -

## Conclusion

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.

## No comments