Help with SUMX and FILTER: Calculate Sales in Country and outside Country

[gmccordoba89]

I'm trying to identify the Total Sales in Country and Outside Country in the following table:

Company Country	Customer Country	Net Sales
Thailand	Thailand	100.0
Thailand	Indonesia	150.0
Thailand	Malasya	50.0
Thailand	Vietnam	40.0
Thailand	Sri Lanka	10.0
Argentina	Argentina	30.0
Argentina	Brasil	60.0
Argentina	Chile	70.0
Argentina	Bolivia	100.0

I have three tables: Customers, Company and Sales. Customer and Company have the Country ID but I don't know how to apply the filter in the CALCULATE formula.

Desire result is two measures: "Sales in Country" and "Sales outside Country". So If I select "Thailand" I should get 100 as "Sales in Country" and 250 as "Sales outside Country". 

[Anonymous]

Hello,

I believe this is the solution you are looking for

SalesInCountry =

SUMX(FILTER(Sheet1, Sheet1[Customer Country ] = Sheet1[Company Country ]), Sheet1[Net Sales ])

and

SalesOutsideCountry =

SUMX(FILTER(Sheet1, Sheet1[Customer Country ] <> Sheet1[Company Country ]), Sheet1[Net Sales ]).

Please let me know if this works for you.

Regards,

Siddhesh H Mane.

[gmccordoba89]

Hi Siddhesh,

Thanks for this. This is exactly the logic I'm loooking for but the Data Model is more complex and each field are in different tables:

Company TBL: Company Country ID, Company Code

Customer TBL: Customer ID, Customer Country ID

Sales TBL: Company ID, Customer ID, Net Sales.

Currently I have an inactive relationship between Company Country ID and Customer Country ID so I think I should use USERRELATIONSHIP function. Anyway my main challenge is how to compare Company Country ID and Customer Country ID.

Thanks again!