How to add and asubtractcolumns from different tables

[nancymccool]

I am creating a column to show how much is left to complete; I am taking to coulms from 1 table and 1 cloumn from another table, but it is not Adding/Subtracting corretly.  It is giving a weird number for every line

 Left to Complete = SUM('FCOs'[Contract Amount])+SUM('FCOs'[Change Order Amount])-SUM('Bill Details'[LineAmount])

[nancymccool]

@v-yanjiang-msft

I dont know what I am doing wong, using your formula and still get the wrong totals.

Its providing the wrong totals;

Ex. Millers Contract is $554,555.00, and I add the change orders $183,981.00 and subtract their invoices $175,022.29, and Left to Complete DOES not equal $565,513.71, it shows as $7,859,734.71.  And yes, the FCOs and Bill Details are attached by relationsip. see below see attached

[v-yanjiang-msft]

Hi @nancymccool ,

According to your snapshot, I find some values are correct (like American Concrete&Precast), but some are wrong, I think it relates to the data model. If you only want to show the result in visual, you can choose a measure instead of column, please try it.

Measure=SUM('FCOs'[Contract Amount])+SUM('FCOs'[Change Order Amount])-SUM('Bill Details'[LineAmount])

Best regards,

Community Support Team_yanjiang

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

[nancymccool]

@v-yanjiang-msft

that is what I had originally, neither are working.  😞

[v-yanjiang-msft]

Hi @nancymccool ,

SUM function in a calculated column do not consider context in each row, it will calculate total value of the whole column. If you want to calculate the value based on each row, directly reference the column like this:

Left to Complete =
'FCOs'[Contract Amount] + 'FCOs'[Change Order Amount] - 'Bill Details'[LineAmount]

Also, this assumes the tables 'FCOs' and 'Bill Details' already have relationship created.

Best regards,

Community Support Team_yanjiang

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