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 DateTransaction TypeTransacted unitsStock NameCumulative figure till date
1.1.2020Buy100A100
2.1.2020Buy50A150
5.1.2020Sell20A130



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.



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.

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




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.

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




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


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 DateTransaction TypeTransacted unitsStock NameCumulative figure till date
1.1.2020Buy100A100
2.1.2020Buy50A150
5.1.2020Sell20A130



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.



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.

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




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.

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




No comments