Forum Discussion
Need help to compare two tables using multiple criteria
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
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
3 Replies
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
Thank you so much Anonymous !!!This is exactly what i was looking for.
Many Thanks.
Best Regards,
Shib
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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
