Compare coverage filtered by country and against global benchmark

[Janet_PHQ]

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, 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]).

Previously what I did is to 'hard code' the total number of events for each country based on different categories, and then I imported this excel table to PBI:

However, to make my visual real time, I designed some measures according to the advice on this post. But in order to have a country % vs global % on a tornado chart like below, I need to design a new table in PBI just like the excel above. 

I am quite new to PBI.  Can anyone offer some advice please? Would really appreciate!

[v-huizhn-msft]

Hi @Janet_PHQ,

Based on my understanding, you your requirement, you'd better create a new table by "New Table" under Modeling on Home page. Because the category values come from different tables. If you want the new table real time, everytime the new table will update as you refresh your resource table by "Refresh" on home page.

Best Regards,
Angelia

[Janet_PHQ]

Thanks Angelia!

Yes but that's the thing I already knew. The problem is, I don't know how to create this table? 😞 Can you advise?

Cheers,

Janet

[v-huizhn-msft]

Hi @Janet_PHQ

Do you mind share your .pbix file for further analysis? So that we can post the solution which is close to your requirement.

Best Regards,
Angelia

[Janet_PHQ]

Thanks for your kind reply!

Sorry the file is over 1G so uploading can't be possible. Also, there is security reason invovled. 

[v-huizhn-msft · Accepted solution]

Hi @Janet_PHQ,

Based on my understanding, create a new table by clicking the "New Table" under Modeling on Home page. Please try the following formula.

NewTable=SUMMARIZE(Table,Table[country],"community",CALCULATE(SUM(Table1[number]),Filter(Table1,Table1[Category]="community")),"concerts",CALCULATE(SUM(Table2[number]),Filter(Table2,Table2[Category]="concerts")),"conference",CALCULATE(SUM(Table3[number]),Filter(Table3,Table1[Category]="conference")),"expos",CALCULATE(SUM(Table4[number]),Filter(Table4,Table1[Category]="expos"))"festival",CALCULATE(SUM(Table4[number]),Filter(Table4,Table1[Category]="festival")))

Please change the Table names as yours.

Best Regards,
Angelia

[Janet_PHQ]

Hi Angelia,

Thanks for your kind advice. Just tried it and it worked like a charm 🙂

Enjoy your weekend!

Janet