DIY 3 : Learn to achieve Conditional Running Total in PowerBI using Example
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
Step 1: Make the Calculated Column
Step 2: Write DAX Expression for getting the desired result
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
)
)
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
Read More: DIY 2: How to achieve Relative Date filtering in Power BI
Apprｅciation to my father who stated to me about this web ѕite,
this web site is truly amazing.
Εхcellent post. І was checking continuously thiѕ blog and I’m imрressed!
Very helpful infoｒmаtion specially the lɑst part :
) I care foг such info much. I was looking for this particular information for a very long tіme.
Τhank yoᥙ and best of luck.