cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
grkanth81 Frequent Visitor
Frequent Visitor

Power Bi Maps Australia

Hello guys,

 

I am trying to create a power BI map report where i would like to know number of employees by state, by city, by suburb.

 

When you click on particular state, it has to take to show count of employees by city and when you click on city it has to show count of employees by suburb.

 

Can you please let me know if the above is possible or not?

 

I tried to do it, but i could achieve only till state and city hierarchy. When i do suburb, it is showing details of other countries also.

 

Please check the data here:

 

https://www.dropbox.com/s/nk1g4pspojxdte7/employee_count.xlsx?dl=0

 

 

 bystate.PNG

 

When you click the drill down on the top left, please check the below by city

bycity.PNG

 

But when you click the drill down to the next level, here is the display:

 

suburbdrilldown.PNG

 

Thanks,

Ravi

2 ACCEPTED SOLUTIONS

Accepted Solutions
deldersveld
Advisor

Re: Power Bi Maps Australia

Try creating a derived column to combine the suburb, city, and country in either Power Query or DAX. I'm assuming you've already petitioned all local governments to change rename everything to be globally unique, and that's been denied :-)

 

Using DAX: Suburb_Derived = Sheet2[Suburb] & ", " & Sheet2[City] & ", " & Sheet2[Country]

 

Suburb.PNG

 

Community Support Team
Community Support Team

Re: Power Bi Maps Australia

Hi @grkanth81,

 

Because there might existing same suburb name in different countries. That is why under suburb level, the map visual shows details of other countries also.

 

In order to uniquely identify suburb across the world, I would suggest you use PostCode. Please see below:

1.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.
7 REPLIES 7
deldersveld
Advisor

Re: Power Bi Maps Australia

Try creating a derived column to combine the suburb, city, and country in either Power Query or DAX. I'm assuming you've already petitioned all local governments to change rename everything to be globally unique, and that's been denied :-)

 

Using DAX: Suburb_Derived = Sheet2[Suburb] & ", " & Sheet2[City] & ", " & Sheet2[Country]

 

Suburb.PNG

 

Community Support Team
Community Support Team

Re: Power Bi Maps Australia

Hi @grkanth81,

 

Because there might existing same suburb name in different countries. That is why under suburb level, the map visual shows details of other countries also.

 

In order to uniquely identify suburb across the world, I would suggest you use PostCode. Please see below:

1.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.
grkanth81 Frequent Visitor
Frequent Visitor

Re: Power Bi Maps Australia

Thanks deldersveld. That worked.

grkanth81 Frequent Visitor
Frequent Visitor

Re: Power Bi Maps Australia

Thanks v-yulgu-msft. That worked using post code option also.

Ringovski Regular Visitor
Regular Visitor

Re: Power Bi Maps Australia

Can you provide a exmaple of this dervied column?

charmainer Member
Member

Re: Power Bi Maps Australia

Hi,

I was hoping if you could point to right place where i could get Australian Lat and log -

i have one downloaded from google but some post codes have inacurate data.

Our organisation has decided to but the data, would you know where i could get this.

Please adivce

kind Regards

Charmaine

plentym New Member
New Member

Re: Power Bi Maps Australia

https://mappify.io/ 

 

I'm just getting started, but so far so good - can provide GeoCodes for up to 2500 addresses per day. 

 

Regards,
Michael