cumulative total in order

SErgey-kuznetso — Tue, 31 Jan 2023 09:15:02 GMT

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

Re: cumulative total in order

FreemanZ — Tue, 31 Jan 2023 09:40:55 GMT

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] )

Re: cumulative total in order

tamerj1 — Tue, 31 Jan 2023 09:48:59 GMT

Hi SErgey-kuznetso

please try

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

topic cumulative total in order in DAX Commands and Tips

cumulative total in order

Re: cumulative total in order

Re: cumulative total in order