Conditional cumulative sum as new calculated column in datamodel

[androo]

In the table below my desired result is the final column (it looks horrid here but pastes nicely enough into Excel).

Index:Reference:PrevRowRef:Amount:Cumulative Amount:Desired Cumulative Result:

1	A				0
2	A	A			0
3	A	A	26.64	26.64	26.64
4	A	A	39.82	66.46	66.46
5	A	A	46.5	112.96	112.96
6	A	A	40.91	153.87	153.87
7	A	A	38.13	192	192
8	A	A	33.63	225.63	225.63
9	A	A	49.64	275.27	275.27
10	A	A	56.09	331.36	331.36
11	A	A	66.21	397.57	397.57
12	A	A	78.79	476.36	476.36
13	A	A		476.36	476.36
14	A	A		476.36	476.36
15	A	A		476.36	476.36
16	B	A		476.36	0
17	B	B		476.36	0
18	B	B	31.06	507.42	31.06
19	B	B	45.94	553.36	77
20	B	B	57.06	610.42	134.06
21	B	B	58.37	668.79	192.43
22	B	B	52.27	721.06	244.7
23	B	B	54.52	775.58	299.22
24	B	B	13.56	789.14	312.78
25	B	B		789.14	312.78
26	B	B		789.14	312.78
27	B	B		789.14	312.78
28	B	B		789.14	312.78
29	B	B		789.14	312.78
30	B	B		789.14	312.78
31	C	B		789.14	0
32	C	C		789.14	0
33	C	C	46.65	835.79	46.65
34	C	C	68.92	904.71	115.57
35	C	C	81.98	986.69	197.55
36	C	C	83.9	1070.59	281.45
37	C	C	77.73	1148.32	359.18
38	C	C	76.34	1224.66	435.52
39	C	C		1224.66	435.52
40	C	C		1224.66	435.52
41	C	C		1224.66	435.52
42	C	C		1224.66	435.52
43	C	C		1224.66	435.52
44	C	C		1224.66	435.52
45	C	C		1224.66	435.52
46	D	C		1224.66	0
47	D	D		1224.66	0
48	D	D	28.24	1252.9	28.24
49	D	D	42.15	1295.05	70.39
50	D	D	45.1	1340.15	115.49
51	D	D		1340.15	115.49
52	D	D		1340.15	115.49
53	D	D		1340.15	115.49
54	D	D		1340.15	115.49
55	D	D		1340.15	115.49
56	D	D		1340.15	115.49
57	D	D		1340.15	115.49
58	D	D		1340.15	115.49
59	D	D		1340.15	115.49
60	D	D		1340.15	115.49
61	E	D		1340.15	0
62	E	E		1340.15	0
63	E	E	22.69	1362.84	22.69
64	E	E	34	1396.84	56.69
65	E	E	47.86	1444.7	104.55
66	E	E	51.44	1496.14	155.99
67	E	E	35.31	1531.45	191.3
68	E	E	35.33	1566.78	226.63
69	E	E	6.53	1573.31	233.16
70	E	E		1573.31	233.16
71	E	E		1573.31	233.16
72	E	E		1573.31	233.16
73	E	E		1573.31	233.16
74	E	E		1573.31	233.16
75	E	E		1573.31	233.16

I have been able to get the "Cumulative Amount" column with this

Cumulative = Var index_1 = 'Calc Table'[Index]

VAR CumSum = if ([PrevRowRef] = [Reference],

    calculate(

        Sum([Amount]),

        Filter(

            'Calc Table',

            'Calc Table'[Index] < index_1

            )

),0)

Return

    CumSum + [Amount]

I have also been able to do this on a sample in Excel (structured references), which doesn't need the Index: or the PrevRowRef: column to do it. But, my data has 500k rows (and this is a cutdown of it there are many other columns too) and I'm concerned that XL may not cope well and also, I'd prefer to build the whole solution in PBi where I can also publish the result explaining it to my colleagues.

[androo]

Thanks very much for this neat solution which runs quickly over my almost 500k lines. I did try using the SUMX but I don't think I've ever got one to work in the way I expected or thought it would and this was no exception. In the past I've generally given up with them but have always been able to find some other way of doing whatever I was trying to do. However, this has brought me some understanding of what it is they are doing and so perhaps I will now find a way to use one and avoid repeating some of my more elaborate past workarounds!

[ryan_mayu · Accepted solution]

@androo 

is this what you want?

Column = sumx(FILTER('Table','Table'[Reference]=EARLIER('Table'[Reference])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[Amount])+0

pls see the attachment below