cancel
Showing results for
Did you mean:
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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Get coverage filtered by country and category

Hi @Janet_PHQ,

```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

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.
12 REPLIES 12
Microsoft

## Re: Get coverage filtered by country and category

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

Proud to be a Datanaut!

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?

Post Partisan

## Re: Get coverage filtered by country and category

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

Helper I

Post Partisan

## Re: Get coverage filtered by country and category

want to see how to create and how you use

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!

Post Partisan

ok

Microsoft

## Re: Get coverage filtered by country and category

Hi @Janet_PHQ,

```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

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.
Post Partisan

## Re: Get coverage filtered by country and category

Thanx

Announcements

#### 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!

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

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

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

Top Solution Authors
Top Kudoed Authors