Adding two measurements from different tables

[laurahoff97]

I have two different tables in powerbi pulled in from a database. The first is overall agreements with four columns for different types of cost and each agreement has an alloted amount for hardware/software/labor/travel. The second table is multiple records that fall under each agreement that have hardward/software/labor/travel costs that are subtracted from the agreements. I will call the agreement table and record table. 

My goal is to add the amounts up from the four columns in each of the tables and then subtract the record cost from the alloted amount in the agreement table measure. So I created measures in both of the tables separately 

Agreementtotal = Agreements[hardwarecost] + Agreements[laborcost] + Agreements[softwarecost] + Agreements[travelcost]

Recordtotal = (Records[hardwarecost] + Records[laborcost] + Records[softwarecost] + Records[travelcost])

The agreement IDs are included in the record table so they have been linked, however, I cannot merge these tables because there are multiple records for each agreement, therefore when merging the agreement table to the record table the agreement numbers are repeated and not calculated correctly.

How can I simply take those two measure from separate tables and subtract them?

I have tried DAX expressions but for some reason my syntax is not being recognized, even when I drag fields directly over from the metadata pane. Below is a simple test I can't even figure out what is wrong with. Have also tried 

test = SUM('Agreements'[software_cost])

 

 Any help is appreciated, thanks!

[Anonymous · Accepted solution]

Hi @laurahoff97 ,

As per your description, I have created two tables and created relationships for them.

Create measure.

AgreementTotal = SUM(Agreements[HardwareCost]) + SUM(Agreements[SoftwareCost]) + SUM(Agreements[LaborCost]) + SUM(Agreements[TravelCost])

RecordTotal = SUM(Records[HardwareCost]) + SUM(Records[SoftwareCost]) + SUM(Records[LaborCost]) + SUM(Records[TravelCost])

RemainingAmount = 
Agreements[AgreementTotal] - 'Agreements'[RecordTotal]

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[laurahoff97]

Thank you! I was unaware I could create a measure in one data table that references columns from another.

[Rena]

This may seem overly obvious, but have you tried making a third measure that simply says:

Total = [AgreementTotal]-[Recordtotal]

[laurahoff97]

They are on different tables and the measure won't pick up a reference to a measure outside of the table you're building it in