Cumulative total minus another column

[vidyasagar159]

Hi All,

Its an enhancement to my earlier request. But not able to crack the logic. Please help.

I simply want to do subtraction of CumulativeTotal - AnotherColumn based on the product category filter. Here is the sample data with some expected outputs.

I was able to achieve the Green Column. But I am stuck on achieving the Yellow column.

Sample data:

Product	Category	Amount_One	Amount_Two	Amount_Three	Cumulative of Amount_Two	Calculated Column 1 = IF(Table1[Category] = “Red”, [Amount_One]-[Amount_Two], [Amount_One]-[Amount_Three]]	Calculated Column 2 = IF(Table1[Category] = “Red”, [Amount_One]-Cumulative of Amount_Two, [Amount_One]-[Amount_Three]]
A	Red	100	10	90	10	90	90
A	Red	200	20	80	30	180	170
A	Red	300	30	70	60	270	240
A	Red	400	40	60	100	360	300
A	Red	500	50	50	150	450	350
A	Red	600	60	40	210	540	390
B	Black	700	70	30	280	670	420
B	Black	800	80	20	360	780	440
B	Black	900	90	10	450	890	450
B	Black	1000	100	0	550	1000	450
B	Black	1100	110	-10	660	1110	440
B	Black	1200	120	-20	780	1220	420

[v-jayw-msft]

Hi @vidyasagar159 ,

I’m not sure whether the logic of calculated column 2 or the outputs in your sample data is what you want.

In that case I created two calculated columns as below:

Calculated Column 2 = IF('Table'[Category] = "Red", [Amount_One]-'Table'[Cumulative of Amount_Two], [Amount_One]-[Amount_Three])

Calculated Column 3 = [Amount_One]-'Table'[Cumulative of Amount_Two]

And the result would be shown as below:

If I misunderstood your meaning, please let me know.

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[parry2k · Accepted solution]

@vidyasagar159 I think this is what you are looking for, soluion is attached.