Forum Discussion

laurahoff97's avatar
laurahoff97
Frequent Visitor
1 year ago
Solved

Adding two measurements from different tables

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!

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

4 Replies

  • Rena's avatar
    Rena
    Icon for Resolver II rankResolver II
    1 year ago

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

     

    Total = [AgreementTotal]-[Recordtotal]

    • laurahoff97's avatar
      laurahoff97
      Frequent Visitor
      1 year ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      laurahoff97
      Frequent Visitor
      1 year ago

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