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.
Hi all,
I need to be able to count the number of products that are unique to each country to be displayed in a column chart but I am unsure how to achieve this.
In the photo below I have created a table with the Product Name, Distinct Count of Product Name and the Count of Country. I need to remove any products that appear in multiple countries (I am aware I can filter the table to only show Count of County equals 1). However, I then need to display this in a column chart to show how many unique products are in each country.
Any help on this would be greatly appreciated, if you need more info please let me know.
@s_mansell , Not very clear try like
sumx(values(Table[Country]),calculate(distinctcount(Table[product])))
Hi @amitchandak ,
Thank you for replying!
Sorry for not being clear. Using emaples from the table in the photo I sent:
The "Acco 3-Hole Punch, Recycled" is a product that is sold in 23 countries therefore it isn't a unique product. Wheras the "Acco 6 Outlet Guardian Basic Surge Suppressor" is only sold in one country therefore it is unique. I now need to associate this product with the coutry it is sold and count how many other unique products are in each and display this in a column chart.
Does this make sense?
Sorry if I'm making a lot of assumptions. First of all, in your data, can one country have multiple products? I know you said one product can be sold in multiple countries, but is the reverse of this true? Do you have data regarding what product is sold in which country? That's the information you need to tie this together. Something like -
Product | Country |
A | USA |
B | UK |
C | Mexico |
C | Canada |
C | Brazil |
C | Argentina |
D | Ecuador |
E | Brazil |
F | Canada |
F | UK |
F | Italy |
G | France |
H | Spain |
I | Spain |
I | Greece |
If you have this data in two different source, you could link them together using product name. There after drop contry name in the axis of the column chart under fields. And product name in values and set it to count. In the visual filter, drop Count Of Country and set the filter to only show products that have one unique country where it is being sold. Hope that helps. If not feel free to poke my brain more. Thank you.
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |