Forum Discussion

vanSiq01's avatar
vanSiq01
Regular Visitor
3 years ago

Running Total with multiple Measures

Hi!!

 

I've been struggling to solve this problem for a few days now. I wanted to calculate a moving sum with multiple measures. I've create an Excel example for it: Excel Example

 

 

In other words, I have a measure called Measure1 and I wanted to accumulate the values of Measure2 starting from it.


However, this accumulation has to follow the operation highlighted in column C. That is, the lines Start Value and Breakdown 1 should be added and from there all the following values should be subtracted.

 

 

 

In the end, it would be something like this: (Start Value + Breakdown 1) - Sum of all other Breakdowns.

 

The only requirement that need to be done only with measures and not with calculated columns.

 

 

DAX
power bi
Running Totals

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi vanSiq01 

    Please try

    Accumulated Value =
    SUMX (
    FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
    IF ( 'Table'[Operation] = "+", 1, -1 ) * 'Table'[Base Value]
    )

    • vanSiq01's avatar
      vanSiq01
      Regular Visitor
      3 years ago

      Hi tamerj1 !

       

      Thank you for reply. Your code is a partial solution for this. Note that, I need from Measure1 (Start Value) add/subtract values of Measures 2 (Breakdowns).

       

      Your code works for only one measure... but I need to start from one measure and add/subtract with the other one.

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        vanSiq01 
        I followed what you have presented. You have presented everything in a table but you never mentioned whether it is a data table or a table visual. You did not provide any description of your data, rather you just presented a data in an excel sheet! Therefore, I assumed that this is a data table. Based on that, I have proposed my solution. If I have the wrong understanding then please clarify.