Forum Discussion
Calculation if ID match
Hey all,
i need to create a calculation that substract an amount from table1 from table2. (Amount from table1 - amount from table2). This should be based on if the ID's match. So if Reference ID from table1 match the ID from table2, then do the substraction, else nothing.
i have posted a screenshot of an example.
8 Replies
create relationship between two tables and create two columns
Column = RELATED('Table 1'[reference id])+0 Column 2 = if(ISBLANK(sumx(FILTER('Table 1','Table 2'[id]='Table 1'[reference id]),'Table 1'[amount])),0,'Table 2'[amount]-sumx(FILTER('Table 1','Table 2'[id]='Table 1'[reference id]),'Table 1'[amount]))pls see the attachment below
Hey, Thanks for the response! There is no relation between these two ID's in the original semantic model. They both relate to the date table i have. Is there a way to add this link with for example TREATAS in the column syntax?
Hi,
In the last row of the desired output table, shouldn't the answer be 300?
Hey, yes. The last row of Desired output should be 300. thanks for highlighting this.
Hi,
In table2, write these calculated column formulas
Table1 reference ID = calculate(max(Table1[reference ID]),filter(Table1,Table1[reference ID]=earlier(Table2[id])))
Amount = calculate(sum(Table1[amount]),filter(Table1,Table1[reference id]=earlier(Table1 reference ID)))
Hope this helps.
