Visualization to show Country only

[sorathecat]

Hi Everyone, I have a data set currently that has columns of information containing city and country. For example: 

When i create a visualization it will show as:

Is there anyway that i can group everything into countries? Currently there is multiple different bars because of different cities. I want to only show the countries and not the cities, for example USA, AUS, NZ, UK etc. Hopefully there is a easy way for it to detect the same countries and add them all up.

Any help will be greatly appreciated! 

[v-xicai · Accepted solution]

Hi @sorathecat ,

You can create columns like DAX below.

Country =
var FirstComma = FIND(",",'Table1'[Location],1)
var SecondComma = FIND(",",'Table1'[Location],FirstComma + 1)
var LengthOfString = LEN('Table1'[Location])
return
MID('Table1'[Location],SecondComma + 1, LengthOfString - SecondComma + 1)
 
City =
var FirstComma = FIND(",",'Table1'[Location],1)
var SecondComma = FIND(",",'Table1'[Location],FirstComma + 1)
return
LEFT(Table1[Location],SecondComma-1)
 

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @sorathecat,

The Power Query Editor makes this possible! The first thing you're going to want to do is to open the query editor and navigate to the "add column" ribbon. You will click on extract, as shown in the visual below.

You will use text after delimiter.  Use the screenshot below to enter in the values.

Once you click ok, you should end up with two columns like the screenshot below.  Make sure to rename the column! This will allow you to group by country.

You can also do this using DAX. If you want to know how to do that, let me know!

Hope this helped!

[sorathecat]

Hi @Anonymous 

Thank you！If you can let me know how to do this as a DAX formula will be great because i am building a dashboard on an app and will be manually updating the excel file every few weeks so i want the visualization to update automatically without much modifications as possible in BI!