Subtracting from columns in different tables

[Anonymous]

Hello Community, 

Trying to subract different columns from different tables and having no luck.   Have tried various formulas and nothing seems to work.   The resulting sum (total and at the row level) is a gigantic number that makes no sense.  

I need to subract "Potential" in the Merge1 table, from "Net Price" in the Sales Orders table.      There is no direct relationship between the tables, but I guess both are indirectly connected thru the Date Table, and thru the Customers Table  (Customer ID).

Est Close date on the Merge1 table is connected to Dates in the Date Table.       Due Date on the Sales_Orders is also connect to Dates.        Customer ID is connect to the same named fields in each table.     All filtering and cross filtering works.  

There is not a one-to-one match between the rows on the tables.  In other words I am not subtracting  Row1 from one table and Row 2 from another table.   There are a lot more rows in the Sales_Orders tables then there are in the Merge1 table.  

I just simply need to subtract the sum of Potential from the sum of Net Price in whatever filter context is selected.   For example, filtering on January should give me the total Potential for January, and the total potential for Net Price   (it does this perfectly now).  What I need is a the correct formula to subtract those two things regardless of filter context   (so with or without out a filter applied).    Thanks in advance for any help!

[Greg_Deckler]

So in general this is going to involve something like:

• RELATED
• RELATEDTABLE
• LOOKUPVALUE
• MAX(FILTER(...)...)

One of those. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Syndicate_Admin]

Hello as I can do in power bi q I take the balance cell and add the must and I subtract the having so I have my accumulated in power bi idem to print q pass, in excel I have it as well..

[Anonymous · Accepted solution]

@Greg_Deckler Thanks Greg. It actually turned out to be a lot simpler. I fixed a relationship and rewrote two simple SUMX formulas.

SumX Potential - SUMX(Merge1,Merge1[Potential]) (and is equivalent to net price)

Then he subtracted them using this.

SumX deviation ([Net Price SumX]-[SumX Potential])

This seems to work well, even if I apply filters (date filter for example)

[v-chuncz-msft]

@Anonymous 

By the way, you may help accept solution. Your contribution is highly appreciated.

[Greg_Deckler]

Glad you got it @Anonymous ! Things definitely tend to work better when you have a solid data model!! 🙂