Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-yulgu-msft
Employee
Employee

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
v-yulgu-msft
Employee
Employee

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.

Hi @v-yulgu-msft

 

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!Smiley Happy

 

Janet

 

 

thnx

Hi Yuliana ,

 

This worked like a charm!! Thank you very much Smiley Very Happy

 

Will accept it as the solution in one second.

 

Cheers,

Thanx

Phil_Seamark
Employee
Employee

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!

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

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!

ok

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.