cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Janet_PHQ Helper I
Helper I

Re: Get coverage filtered by country and category

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

 

 

expo.PNG

 

Table 2

 

festival.PNG

 

Table 3

 

conference.PNG

 

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?

 

sports.PNG

 

Please help. Thanks Smiley Very Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: Get coverage filtered by country and category

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

3.PNG

 

Matrix visual

2.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
Microsoft Phil_Seamark
Microsoft

Re: Get coverage filtered by country and category

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) ?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Janet_PHQ Helper I
Helper I

Re: Get coverage filtered by country and category

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? 

mdaamirkhan Post Partisan
Post Partisan

Re: Get coverage filtered by country and category

it is possible to share file it willl help me for further details.

Janet_PHQ Helper I
Helper I

Re: Get coverage filtered by country and category

Thanks but I already shared some tables above. Can you please advise based on what's available?

mdaamirkhan Post Partisan
Post Partisan

Re: Get coverage filtered by country and category

want to see how to create and how you use

coverage filtered by country and category

 

Janet_PHQ Helper I
Helper I

Re: Get coverage filtered by country and category

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!

mdaamirkhan Post Partisan
Post Partisan

Re: Get coverage filtered by country and category

ok

Microsoft v-yulgu-msft
Microsoft

Re: Get coverage filtered by country and category

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

3.PNG

 

Matrix visual

2.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

mdaamirkhan Post Partisan
Post Partisan

Re: Get coverage filtered by country and category

Thanx

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors