Forum Discussion

alya1's avatar
alya1
Icon for Helper V rankHelper V
1 year ago
Solved

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", the...
Need Help
  • alya1's avatar
    alya1
    1 year ago

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

parry2k's avatar
parry2k
Icon for Super User rankSuper User
1 year ago

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