Forum Discussion
Subtract values in same table but different rows based on matching Subtract from ID and ID column
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!
It did not, I ended up just creating another table to with Subtract from ID as primary key.
3 Replies
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] )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 TeamIt did not, I ended up just creating another table to with Subtract from ID as primary key.
