Total based on sequence

[lanzarote5]

Hi all,

I have a dataset with a certain sequence. At every sequence point, I have a value added, and a value substracted. 

Now I would like to have a calculation based on this (Added-Substracted), but not only for the row, but also for previous rows in the sequence (where the sequence number is lower than in the current row). It should also only count this for the relevant Category.

So in the end I would like to achieve something like the column "Total value", which I could use in a visual.

Category	Sequence	Added	Substracted	Total value
A	1	5	0	5
A	2	4	1	8
A	3	5	3	10
A	4	10	8	12
B	1	2	0	2
B	2	8	1	9
B	3	5	6	8
B	4	6	4	10
B	5	12	2	20

I have no idea how to solve this. Especially how to calculate previous rows. I tried somethings like this in DAX:

Cumulative = 

    CALCULATE (
        SUMX ( Table1[Added]- Table1[Substracted]),
        FILTER (
            Table1,
            Table1[Sequence] <=Table1[Sequence]
                && ( Table1[Category] ) = ( Table1[Category]) 
) )

[v-jiascu-msft · Accepted solution]

Hi @lanzarote5,

Try the formula below please.

Column =
CALCULATE (
    SUM ( Table1[Added] ) - SUM ( Table1[Substracted] ),
    FILTER (
        ALLEXCEPT ( 'Table1', Table1[Category] ),
        Table1[Sequence] <= EARLIER ( Table1[Sequence] )
    )
)

Best Regards,

Dale

[lanzarote5]

Great! That works well. 🙂