Microsoft® Excel® 2013: Using Power View

  • 3/15/2013
Contents

Using maps

All the charts you have seen in Power View up to now are somewhat familiar to Excel users because they exist, although with some differences, in standard Excel. The map chart is a wonderful addition to Power View and, as you might imagine, it lets you draw charts on a map. Like all other Power View features, the beauty of the map chart is its simplicity.

To build a map chart, you need to have information about the locations of sales. In AdventureWorks, each customer contains the complete address through the DimGeography table, and you are going to use this information to create a map out of it. You find this data model in the companion workbook “CH10-02-Power View Map.xlsx.”

Note

To complete this demo, you are not using just the information in DimSalesTerritory, which was already present in the previously used data model, because DimSalesTerritory contains only information at the country level. For the map chart, you want to get complete addresses. Thus, you are going to use customer addresses to find precise points on the map.

In Figure 10-26, you can see the data model for this demo.

Figure 10-26

Figure 10-26. The data model for the map demo contains the DimGeography table.

To start, simply select the Sales calculated field in a newly created Power View report and convert the table into a map by clicking the Map button on the DESIGN tab of the ribbon. After a bit of resizing, you end up with a map of the world that does not contain any information about sales. If you look at the field list, you will note that some columns have a world icon beside them; namely, City, EnglishCountryRegionName, and PostalCode.

In fact, Excel analyzed the content of the table and, having detected city and location names inside these objects, marked them as geographical columns. To draw circles on the map and start looking at the map, it is enough at this point to select EnglishCountryRegionName and put it into the LOCATIONS panel to project data using the geographical location of the region.

Important

The first time you create a map using the data mode, a privacy warning appears, saying that the data need to be sent to Bing in order to be geocoded. This is because Excel does not contain a map of the world, with cities and addresses. In order to take the latitude and longitude of your addresses, Excel sends them to Bing, which in turn returns the coordinates of the addresses. For this reason, you can create map charts only if you have an active Internet connection, which is also required to download the map from Bing.

At this point, your chart will look like Figure 10-27.

Figure 10-27

Figure 10-27. The map chart is incredibly easy to create using geographical names as the source.

This chart is amazingly simple to create. When you zoom into Europe, you will see that the points drawn are at the country level only—there is no information about the cities. The reason for this is pretty obvious: you used the country name as the slicer. To get more detailed information, you can try to add the city after the country in the LOCATION panel, but surprisingly, nothing changes in the report after you do this. In fact, even if you are able to add many columns to the LOCATION panel, only the first one is shown on the map. All the remaining ones are used for drill-down operations, which you will learn later.

A way to get more detailed information might be to remove the country and leave the city in the LOCATION pane. After a few seconds, during which geographical resolution happens, you will see many points in the map, and if you zoom on Paris in France, you will see the report shown in Figure 10-28.

Figure 10-28

Figure 10-28. Using the city, the map is much more detailed, showing cities instead of countries.

Unfortunately, there are at least two cities named Paris in the world: there is one in France, and there is another in Texas. When you say that you want to plot data for the city of Paris, which one do you mean? You clearly need a way to distinguish between these two (and possibly others as well).

Because only one column can be used on the map, the solution is to create a calculated column that concatenates the various pieces of the address into a single place. The expression for the calculated column is as follows:

FullCityName =

    DimGeography[City] & ", " &
    DimGeography[StateProvinceName] & ", " &
    DimGeography[EnglishCountryRegionName]

Now each column contains the full specification of the city (that is, the city, the state or province, and the country). By using this column, you will get detailed information about locations, with no ambiguity.

When drawing maps, you can even add more information to your chart by using a separation by color. In Figure 10-29, you can see a map where we assigned the category to the color axis and added a slicer for the country region. You can see that each point in the map has turned into a small pie chart, colored as the legend indicates.

Figure 10-29

Figure 10-29. When you add a color axis, each point is turned into a small pie chart and colored by category.

You can modify the way a map chart is shown on the LAYOUT tab of the Excel ribbon. You can change the map background to make the map look like satellite view or look like a road map, modify the legend, and other cosmetic options.

Save to your account

This chapter is from the book