I have created a model based on data from http://www.scb.se/en_/ to visualize the number of households in Sweden.
The data was split by municipalities and I could also find a list of municipalities and their link to Swedish regions – and then it was no problem plotting these in a power view sheet in Excel
And the user can double click the region to drill down to municipality level
Very nice – and I was ready to deploy the model to Sharepoint – Excel Services so the users could browse the model online.
But what a surprise when I double clicked the Stockholm region in Excel Services…
Suddenly I have a municipality in Unites States – Salem – it turns out that the map in Excel services ignores the map hierarchy information from the parent – so it picks the first Salem returned by the Bing Maps Service.
So I added a calculated field in my Map table where I combine Municipality and Country – and mark it as County in the Reporting Properties – Data Category.
And know it works in Excel Services as well when drilling down in the Stockholm Region
It works like expected in Excel services as well.
So you might consider to add the country to all your locations just to be on the safe side, when you don’t have latitude and longitude for the locations.