Need help to compare two tables using multiple criteria

[coolshib]

Hello Everyone,

I have following tables

Location: 1
Company	Product	Sale
ABC	Apple	100
XXX	Orange	200
YYY	Pear	300
ZZZ	Banana	400

Location: 2
Company	Product	Sale
ABC	Apple	300
XXX	Orange	150
YYY	Pear	200
ZZZ	Banana	450
XYZ	Cherry	800
DYB	Plum	200
ZYX	Guava	500

Now i need to compare product wise company wise total sales of location 1 with location 2.

Company	Product	Sales Location 1	Sales Location 2
ABC	Apple
XXX	Orange
YYY	Pear
ZZZ	Banana

Many Thanks in advance.

Best Regards,

Shib

[amitchandak]

Create common company and product dimension and join both tables with them . Sales from location 1 is location 1 sales.

company = distinct(location1[company])

Or

company = distinct(union(distinct(location1[company]),distinct(location2[company])))

Same way create product

or create a new table like

union (summarize(location1,location1[company],location1[Product],"Sales Location 1",sum(location1[sales]),"Sales Location 2",0.0),
summarize(location2,location1[company],location2[Product],"Sales Location 1",0.0,"Sales Location 2",sum(location2[sales]))
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[V-pazhen-msft · Accepted solution]

@coolshib 

Once you have created a relationship between the tables, you could create measure that returns the sales in location 2. 

Sale location2 = CALCULATE(SUM(Location2[Sale]),
FILTER(Location1,Location1[Product] in VALUES(Location2[Product])),
FILTER(Location1,Location1[Company] in VALUES(Location2[Company])))

Paul Zheng

[coolshib]

Thank you so much @V-pazhen-msft !!!This is exactly what i was looking for.

Many Thanks.

Best Regards,

Shib