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

Re: Compare coverage filtered by country and against global benchmark

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.

 

conference.PNG

 

expo.PNG

 

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

 

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:

 

Excel hard coded.PNG


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. 

 

Global VS Individual.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: Compare coverage filtered by country and against global benchmark

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

View solution in original post

6 REPLIES 6
Microsoft
Microsoft

Re: Compare coverage filtered by country and against global benchmark

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 Helper I
Helper I

Re: Compare coverage filtered by country and against global benchmark

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

Microsoft
Microsoft

Re: Compare coverage filtered by country and against global benchmark

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 Helper I
Helper I

Re: Compare coverage filtered by country and against global benchmark

Thanks for your kind reply!

 

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

Microsoft
Microsoft

Re: Compare coverage filtered by country and against global benchmark

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

View solution in original post

Janet_PHQ Helper I
Helper I

Re: Compare coverage filtered by country and against global benchmark

Hi Angelia,

 

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

 

Enjoy your weekend!

 

Janet

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors