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 there, I have 5 tables: Table 1 is expo events (all category = expo), table 2 festival events (all category = festival), table 3 conference events (all category = conference). The 4th table is a list of unique countries including country code, country names etc. The last one is a small table for a list of unique categories appeared in the big table. I joined table 1,2,3 with table 4 by country code, and table 1,2,3 with table 5 by category ID. Some sample screenshots for table 1,2,3 are below for your reference.
Table 1
Table 2
Table 3
Table 1, 2, 3 each has information for a list of countries (some tables may have over 100 countries). I already created a column "Count" and make it all equal to 1 for each table 1,2, 3. Then created a measure "Total" = count([count]).
Now what I wanna do is to get the expo %, festival % and conference % for each country (say 30% + 25% + 45% = total 100%). For example, for Brazil, I wanna calculate the total expo events for Brazil and then total events for Brazil, then divide them to get the % of expo events for Brazil. And I wanna do the same for other 99 countries too if it makes sense to you. Just like the graph showed on my original post here.
The question is, how can I get a calculated measure across multiple tables and filter by the country column, and add up different categories for each country to get the total number of events? I'm thinking of something like CALCULATE(count[count], filter[country]), but if later I need to put the expo %, festival % and conference % all on a single graph (like below graph), is this possible? Or do I need to create a calculated measure for each country so 100 times for 100 countries?
Please help. Thanks
Solved! Go to Solution.
Hi @Janet_PHQ,
Please try these measures:
total Measure1 = CALCULATE(COUNT(Table1[Country Name])) total Measure2 = COUNT(Table2[Country Name]) total Measure3 = COUNT(Table3[Country Name]) total Measure all = [total Measure1]+[total Measure2]+[total Measure3] expo % = [total Measure1]/[total Measure all] festival % = [total Measure2]/[total Measure all] conference % = [total Measure3]/[total Measure all]
Table relationships
Matrix visual
Best regards,
Yuliana Gu
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 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |