cumulative total in order

[SErgey-kuznetso]

Hello everyone! I need to calculate a cumulative total column. But the calculation should take place within each client's email in order of their index from smaller to larger. I have looked at other solutions on this topic, but they don't work for me because they calculate the total sum for the client. Is there a way to create a calculated column so that it adds up all the commissions for the client in order of the index?

I have a such data

Table 1:

emailclient	period_(30_days)	commission	indx
test@test.com	0	300	1
test@test.com	240	5217	2
test@test.com	270	325	3
test@test.com	360	3434	4
test@test.com	510	3608	5
test@test.com	540	374	6
test01@test.com	0	563	1
test01@test.com	30	926	2
test01@test.com	210	450	3
test01@test.com	690	865	4
test01@test.com	720	320	5

As a result, I should get a column like this "cumulative_commission"

Table 2:

emailclient	period_(30_days)	commission	indx	cumulative_commission
test@test.com	0	300	1	300
test@test.com	240	5217	2	5517
test@test.com	270	325	3	5842
test@test.com	360	3434	4	9276
test@test.com	510	3608	5	12884
test@test.com	540	374	6	13258
test01@test.com	0	563	1	563
test01@test.com	30	926	2	1489
test01@test.com	210	450	3	1939
test01@test.com	690	865	4	2804
test01@test.com	720	320	5	3124

[tamerj1]

Hi @SErgey-kuznetso 

please try

cumulative_commission =
SUMX (
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[emailclient] ) ),
'Table'[index] <= EARLIER ( 'Table'[index] )
),
'Table'[commission]
)

[FreemanZ · Accepted solution]

hi @SErgey-kuznetso 

try to add a column like this:

cumulative_commission2 = 
VAR _client = [emailclient]
VAR _index = [index]
RETURN
SUMX(
    FILTER(
        TableName,
        TableName[emailclient] =_client
            &&TableName[index]<=_index
    ),    
    TableName[commission]
)