Last week I introduced Power View by creating a simple table and then a chart from that table. This week, I’ll do a quick overview of another visualization within Power View, the ability to display your data on a map.
To begin, I open Excel and build a data model with the data I want to visualize in a map. I need to specify location information using some of the fields in the data model. In theory, I can use anything that identifies where the measure I’m displaying takes place. Ideally, I would like to have latitude and longitude for each fact instance in the fact table, but that is not always possible or even necessary. For example, let’s begin by looking at the relative sales by city from the Contoso dataset.
After opening my Excel spreadsheet and building an appropriate data model, I return to the Excel window and from the Insert tab click the Power View button in the reports section.
This opens a new worksheet as shown below with a blank design area on the left and my field list from my data model on the right.
I then drag the fields I want to use in my data visualization to the Fields box at the bottom of the right panel. For this example, I will drag the CityName field from the Geography dimension and the SalesAmount field from the FactSales table. This gives me the two column table shown below with sorted by the city by default.
To change the visualization, I need to open the Design tab which appears when I click anywhere within the table in my design area. If I had multiple tables, I would have to be sure to click in the table for which I want to change the visualization first. Then from the Switch Visualization group, I select Map.
The default visualization, shown below, displays a bubble for each city that I have data. Each bubble’s size represents the relative sales amount derived from that city.
Because I did not specify a field to use as a group level for color, all of the bubbles initially appear the same color. However, I can easily specify a different color for each country by copying the field RegionCountryName to the Color parameter. This assigns a unique color to the bubbles within a country that is different compared with other countries. At first glance, everything may appear to be okay, but then I noticed a bubble in the southeast portion of the United States that had a different color. Hovering over that bubble, I see information about the bubble including the city name, the country, and the sales amount. In this case, the city is Saint Petersburg. However the country is Russia, not the United States (Saint Petersburg, Florida is perhaps what the map was thinking.) This occurred because the location criteria was only based only on city, not city within the country. In fact, if I zoom into the map further, I find other bubbles that placed the city in the wrong country.
One way to fix this issue is to use a field that has both the city name and country in it. However, you cannot create a new calculated column from within Power View. This type of change must occur in the data model. Therefore, I could return to the data model and open the DimGeography table to create a new concatenated field. This field combines the city and country names into a single new field: City_Country using the following formula:
= [CityName] & “, ” & [RegionCountryName]
The resulting new column appears in the following figure.
If I replace the [CityName] field with the [City_Country] field in the locations box as shown below, it appears at first glance that the problems with incorrectly positioned cities have been solved.
But again if I expand the map, I can find a few cities such as the one shown in the figure below that are not correctly positioned.
Honestly, I have not been able to figure out why a few cities are still displayed incorrectly. However, I have another way I can ‘fix’ the problem. First I turn on the Filters Area which has been turned off to maximize the size of the map.
I then drag the RegionCountryName field from the DimGeograph table over to the Filter panel. This shows me a list of unique values for this field. I can then use the check box to select one or more countries to display on the map at one time. For example, let’s just display the United States.
When I add the filter, the bubble for Cheshire, United Kingdom disappears. As you can see in the following figure. Changing the map background to Road Map Background, I get a more colorful map that might be more suited for a report that appears in color.
However, this is not the only way to filter data. I can also create a slicer in the design area by dragging the field by which I want to select data and dropping it in an empty part of the design area. The figure below shows me dragging the field RegionCountryName to the design area to the right of the map legend. This initially creates a single column table with the values from this field.
Next, without leaving the field, I can go to the Design tab to the right of the Power View tab and select the Slicer button from the Slicer group. This action converts the table into a slicer object that controls all the other objects in the current page.
Now I can filter the map to any country I want to focus on. Typically, selecting a country also zooms into the map to display that country as shown in the following image in which I selected Japan.
Any time that I want to return to the map displaying all the countries again I can click on the small blue ‘eraser’ button in the upper right corner of the slicer table. Note that this button only appears while the mouse is hovering within the slicer.
That’s it for this week. Next time I will look at some other features of Power View. C’ya.