Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

cumulative sum

Hello I would like to obtain this result

I want to make a cumulative sum each in relation to my previous value, my base value is that of B

indexAB
11015
220=15+20=35
330=35+30=65
440=65+40=105


Result:

IndexAB
11015
22035
33065
440105

earlier doesn't work
B =
IF (
    test[Index] = 1, 
    test[B]
    CALCULATE(
        SUMX(
            FILTER(
                test,
               test[Index] <= EARLIER(test[Index])  
            ),
            test[B]  
        )
    )
)
thank you for your feedback
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,


    powerbidev123's reply could return the result you want. However if A1 is not 10, it will not work. I don't suggest you to use EARLIER() function, it may cause bad performance if you have large size of data.

    You can also try my code to create a calculated column.

    B =
VAR _B1 = 15
VAR _CurrentIndex = 'Table'[index]
RETURN
    IF (
        'Table'[index] = 1,
        _B1,
        _B1
            + CALCULATE (
                SUM ( 'Table'[A] ),
                FILTER ( 'Table', 'Table'[index] > 1 && 'Table'[index] <= _CurrentIndex )
            )
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

7 Replies

  • powerbidev123's avatar
    powerbidev123
    Solution Sage
    1 year ago

    Hi Anonymous , Let me know if this helps

    B =
    5 + SUMX(
        FILTER(
            'Table (3)',
            'Table (3)'[Index] <= EARLIER('Table (3)'[Index])
        ),
        'Table (3)'[A]
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,


    powerbidev123's reply could return the result you want. However if A1 is not 10, it will not work. I don't suggest you to use EARLIER() function, it may cause bad performance if you have large size of data.

    You can also try my code to create a calculated column.

    B =
VAR _B1 = 15
VAR _CurrentIndex = 'Table'[index]
RETURN
    IF (
        'Table'[index] = 1,
        _B1,
        _B1
            + CALCULATE (
                SUM ( 'Table'[A] ),
                FILTER ( 'Table', 'Table'[index] > 1 && 'Table'[index] <= _CurrentIndex )
            )
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Anonymous , Try using 

    Cumulative B =
    CALCULATE(
    SUM(test[A]),
    FILTER(
    ALL(test),
    test[Index] <= MAX(test[Index])
    )
    ) + MIN(test[B])

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      hi bhanu_gautam thank you for your feedback, but I would like to fill my column B from the second and each time by making my value from the current line of my column A + my last value from the previous line of column B

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

       

      • bhanu_gautam's avatar
        bhanu_gautam
        Super User
        1 year ago

        Anonymous , Try using

         

        B =
        IF (
        test[Index] = 1,
        test[B],
        test[A] +
        CALCULATE(
        MAX(test[B]),
        FILTER(
        test,
        test[Index] = EARLIER(test[Index]) - 1
        )
        )
        )