Subtract values in same table but different rows based on matching Subtract from ID and ID column

[alya1]

Hello,
I feel like the below should be possible without splitting the table/creating relationships but I am having a bit of trouble.
I want to create a new column where if "Different" is "Yes", then subtract the Difference from Amount 2 of ID (on a different row) that matches Subtract from ID column.
original table

ID	Amount 1	Amount 2	Difference	Different	Subtract from ID
111	5	5	0	No
222	10	5	5	Yes	111

final table

ID	Amount 1	Amount 2	Difference	Different	Subtract from ID	New Col
111	5	5	0	No		0
222	10	5	5	Yes	111	5

Thank you!

[Anonymous]

Thanks for the reply from parry2k.

Hi  @alya1 , 

Did parry2k 's method solve your problem? If yes, could you please mark it as solution? This will be of great help to other users experiencing similar problems. Thank you for your kind cooperation!

If you still have questions, please feel free to ask me. 

Best Regards,
Zhu
Community Support Team

[alya1 · Accepted solution]

It did not, I ended up just creating another table to with Subtract from ID as primary key. 

[parry2k]

@alya1 add new column using this expression, change table and column name as per your model:

New Column = 
IF ( 
    Substract[Different] = "Yes", 
    VAR __substractId = Substract[Subtract from ID]
    VAR __Amount2 = CALCULATE ( MAX ( Substract[Amount 2] ), FILTER ( ALL ( Substract ), Substract[ID] = __substractId ) )
    VAR __Difference = Substract[Amount 1] - __Amount2
    RETURN 
        __Difference,
    Substract[Difference] 
)