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
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
Hope you are well!
Thanks for solving my problem the other day. Can I ask you one more question based on the help you provided please?
So now I have the real-time measures, but I couldn't manipulate the measures to get a chart like the tornado chart presented earlier in my first post http://community.powerbi.com/t5/Desktop/Get-global-coverage-based-on-conditions/m-p/224326/highlight...
Basically, what I eventually want is a chart that has the global % on the left of the tornado and category coverage % for each country on the right. After selecting a single country, I can see which country has good coverage against the global % in terms of different categories. Hope this makes sense to you?
Please advise. Thank you a lot!
Janet
thnx
Hi Yuliana ,
This worked like a charm!! Thank you very much
Will accept it as the solution in one second.
Cheers,
Thanx
Hi @Janet_PHQ
Given the structure of the tables are so similar, have you considered stacking them on top of each other either in the Query Editory (using APPEND) or in DAX (using UNION) ?
Hi Phil,
Thanks but if I append them, I would have over millions data that will also grow with each day.
Is there any other way?
it is possible to share file it willl help me for further details.
Thanks but I already shared some tables above. Can you please advise based on what's available?
want to see how to create and how you use
so it means that, for example, for Australia, it has expo(say 20 events)/festival(say 30 events)/conference(say 50 events) which are spread across the 3 tables. If I filter by country `Australia`, I want to get the percentage for:
expo % = 20 / 100 = 20%
festival % = 30 / 100 = 30%
conference % = 50 / 100 = 50%
I need to get the % above for each country and be able to compare them on a single chart if you know what I mean.
Thanks!
ok
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |