Row Total for a measure that evaluates two columns.

[Anonymous]

Hi All,

I'm fairly new to power bi and I've come across an issue that I cannot solve. Let me explain the situation.  I'm pulling data from two tables. Table1 has an invoice cost and Table2 has an actual cost(complete tables are included in the screenshot for reference). I've created a measure, Diff = sum(Table1[invoice cost]) - sum(Table2[) that properly calculates the difference on a line item level. However, for row totals the measure calculates invoice cost - sum(actual cost).  I've turned off row subtotals for the table on the right, example B, but this doesn't solve the issue because I'd still like to be able have a total for the difference, either a row total or a card with a total difference measure. I've looked at other solutions to solve this issue but those solutions(sumx, filters) are all for measures that just look at one table. 

Any help would be greatly appreciated! 

Thank you!

[MFelix]

Hi @Anonymous ,

To understand better your model can you please check the questions below:

• Are table 1 and Table 2 related in some way? If yes how?
• What is the final result you want to get on the total column? 
  • 4 for the second table = 0,5 -2 -3 = 4

Regards,

MFelix

[Anonymous]

Hi @MFelix 

Table 1 and Table 2 are not directly related, they are connected through a lookup tables. So there's a customer lookup table that contains all unique customer values between the two tables and a product lookup table that does the same. Table 1 and table 2 are both connected to the lookup tables but not too each other. If I try to connect the two in the actual model it creates a many to many connection and because of ambiguity won't let me activate it. 

To answer your second question: it should equal -4.5=0.5-2-3

Thank you for your help!

[MFelix · Accepted solution]

Hi @Anonymous ,

Keeping the measure that you already have add the following measure:

Total_Diference = 

SUMX(ADDCOLUMNS(Table2;"Difference";[Diff]);[Difference])

Should work as expected.

Regards,

MFelix

[Anonymous]

It worked! Thank you so much! @MFelix