Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
@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 @V-pazhen-msft !!!This is exactly what i was looking for.
Many Thanks.
Best Regards,
Shib
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |