Mapping Data in Power View
Data visualization in Power View is not limited to tables and charts. Another powerful technique that you can use to both analyze and present your insights is to display the data in map form. All that this requires is that your source data contains information that can be used for geographical representation. So if you have country, state, town, or even latitude and longitude in the data set, then you can get Power View to add a map to your report and show the selected data using the map as a background.
Better yet, a Power View map behaves just like any other visualization. This means that you can filter the data that is displayed in a map, as well as highlighting it, just as you can do for charts, tables, and matrices. Indeed, you can even drill down into maps just like you can with matrices and charts. Not only that, but a map is an integral part of a Power View report. So if you highlight data in a chart, a map in the same report will also be highlighted.
The aim of this chapter is to show you some of the ways in which you can add real spice to your reports by using maps. Then, when presenting your analyses, you can interact with the maps and really—no really—impress your audience.
Before adding a first map I want to explain how mapping works in Power View. The geographical component is based on Bing Maps. So, in order to add a map, you need to be able to connect to the Internet and use the Bing Maps service. Secondly, the underlying data set must contain fields that are recognized by Bing Maps as geographical data. In other words you need country, state, town, or other information that Bing Maps can use to generate the plot of the map. Fortunately Power View will indicate if it recognizes a field as containing data that it can use (hopefully) to create a map, as it will display a tiny icon of a globe in the Field List for every field in the underlying data set that apparently contains geographical data.
To avoid the risk of misinterpreting data you can add metadata to the underlying PowerPivot data model which will define geographical field types. This is explained in Chapter 11 where you will see how to apply data categories to fields. Power View maps will then use these categories to interpret geographical data for mapping.
Preparing data so that any fields used by Bing Maps are not only recognizable as containing geographic data, but are also uniquely recognizable, is vital. You must help Bing Maps so that if you are mapping data for a city named Paris, Bing can see whether you mean Paris, France or Paris, Texas. Chapter 11 explains some of the ways in which you can prepare your data for use by Bing Maps, and consequently use it to add map visualizations to Power View.
Although preparing the data may be necessary, it is certainly a lot simpler than the alternatives that involve finding, loading, and understanding shape files for geographical representation, or manipulating geospatial data in a database such as SQL Server.
Note There are some areas of the world that cannot use Bing Maps. So if you attempt to use Power View mapping in these geographical zones, you will not see any map appear when you attempt to create a map.
Let’s begin by creating a map of Sales by Country. Fortunately the sample data set contains the country where the sale was made. This means that we can use this data to make Power View display a map of our worldwide sales. Here is how to create an initial map:
- Insert a new Power View sheet. You need to do this because maps tend to need most, if not all, of the available space in a Power View report.
- Add the following fields to the FIELDS box in the Layout section of the Field List. You will see a table appear in the Power View canvas containing the data.
- CountryName
- GrossMargin
- Filter the report to show only data for 2013 (this is described in Chapter 3 if you have started the book at the current chapter).
- Click Map in the Design ribbon.
- If this is the first time that you are using the Map function, then Power View will display the alert shown in Figure 8-1.
Figure 8-1. The Bing Maps privacy warning
- Click Enable Content to allow Bing Maps to be used.
- The table will be replaced by a map representing the data for each country. You can now resize the map to make it easier to read. It should look something like Figure 8-2.
Figure 8-2. An initial Power View map
It is probably worth clarifying a few points about maps in Power View before we go any further. The essential points to note are
- A map is a visualization like any other. You can resize and move it anywhere on the Power View canvas.
- The map will apply any filters that have been set for the report.
- Each data point (or bubble) in a map is proportional to the relative size of the underlying data.
- You can hover the mouse pointer over a data “bubble” to display a popup showing the exact data that is represented. An example of this is shown in Figure 8-3.
Figure 8-3. Displaying the exact data in a Power View map
Adjusting Map Display in Power View
Creating a map is, as you have seen, extremely easy. However the initial map is not necessarily the finalized version that you wish to show to your audience. You may wish to
- Position the map elements more precisely inside the visualization.
- Zoom in or out of the map.
- Remove or add a map title.
- Select a different type of map background.
- Filter the underlying data.
In the next few sections we will look at these various modifications that you can make to Power View maps. Hopefully you will find these tweaks both intuitive and easy to implement. In any case, with a little practice you should find that these modifications take only a few seconds to accomplish.
If the area displayed in a map is not quite as perfect as you would prefer, then you can alter the area (whether it is a country or a region) that appears in the map visualization. To do this
- Place the mouse pointer over the map. The Zoom and Pan icons will appear at the top right of the map.
- Click on the Pan Right icon (the right-facing triangle) to alter the geographical area displayed in the map visualization.
The icons used to pan a map appear in Figure 8-4.
Figure 8-4. Panning around a Power View map
Tip You can also click inside a map and drag the mouse to pan around.
It is conceivable that the map that is displayed is not at a scale, which you would prefer. Fortunately this is extremely easy to fix. All you have to do is
- Place the mouse pointer over the map. The Zoom and Pan icons will appear at the top right of the map.
- Click on the Zoom Out button (the minus sign) to zoom out, or on the Zoom In button (the plus sign) to zoom in.
Tip You can also move the mouse scroll wheel to zoom in or out of a map.
Removing or Adding a Map Title
A map adds a title automatically to explain what data you are seeing. You can remove (or add back) a title like this:
- Click on the map visualization to select it.
- In the Layout ribbon click Title.
- Select None.
The title will be removed from the map. To add a title back to a map, all you have to do is to carry out the first two steps and then select Above Map instead of None.
You can choose between any one of five map types when using maps in Power View. By default, a map is displayed as a Grayscale Road Map Background. Yet you can alter the map display in the following way:
- Click on the map to select it.
- In the Layout ribbon, click Map Background, and select Aerial (Satellite Photo) Map Background.
Your map will now look like Figure 8-5.
Figure 8-5. Changing the background of a Power View map
Power View allows you to choose from five available map backgrounds. These are explained in Table 8-1.
Table 8-1. Map Background Options
|
Map Background Option |
Description |
|---|---|
|
Road Map Background |
Displays the classic road map with colors. |
|
Grayscale Road Map Background |
Shows the road map using monochrome representation of the geographical data and colors for the display data. |
|
Reverse Grayscale Road Map Background |
Shows the road map but using dark grayscale for the geographical data and colors for the display data. |
|
Aerial (satellite photo) Map Background |
Shows a satellite view of the map. |
|
Grayscale Aerial Map Background |
Shows a satellite view of the map using monochrome representation of the geographical data. |
A map can be filtered to display only specific data, just like any other Power View visualization. This is virtually identical to the way in which you saw how to filter data in Chapter 3, but as a quick revision, here is how to exclude, say, data for the United States from the list of countries in a Power View map.
- Display the Filters Area (unless it is already visible).
- Click on a map; you will see Map at the top of the Filters Area.
- Click on the word Map. You will see the filters for the fields that are used in the map visualization.
- Expand the CountryName field.
- Click (All). A check mark will appear for each country.
- Uncheck the box for United States.
The data bubble for the United States will be removed from the map, leaving the other countries visible. This is shown in Figure 8-6.
Figure 8-6. Filtering data for a Power View map
You can use all the filtering techniques described in Chapter 3 to filter a map. Rather than reiterate all the options here, I suggest that you refer back to that chapter for full details.
Tip If you know that you will only want to see the data for a subset of geographical locations, then it can be worth defining a filter to display only the areas that interest you before you switch to a map visualization. This way the initial map will only show the selected locations, which could save you from having to zoom and pan the map.
Multi-Value Series
So far we have seen how you can add a single data series to a map and have the data represented as a data point. Power View can extend this paradigm by allowing you to display the data bubble as a pie that contains a second data series—and consequently display the data broken down by a specific data set per geographical entity.
As an example of this (and to revise the some of the map creation techniques that we have seen so far), let’s try to analyze European car sales by age range.
- Insert a new Power View sheet.
- Display the Filters Area (if necessary) and add the Year field from the YearHierarchy in the Date table.
- Adjust the Year slider to select only the year 2013.
- Add the following fields to the FIELDS box in the Layout section of the Field List:
- CountryName
- CarAgeBucket
- SalePrice
- Filter the table to exclude the United States as described in the previous section.
- Click Map in the Design ribbon.
- Resize the map visualization.
The map now contains a legend for the CarAgeBucket, and has added the CarAgeBucket field to the COLOR box of the Field List Layout section. Each bubble is now a pie of data. The overall size of the pie represents, proportionally, the sum total data compared to the other pies. The map should now look like Figure 8-7.
Figure 8-7. Displaying pie charts in a Power View map
It is worth remarking that if you hover the mouse pointer over the data representation (the pie) for a country, it will expand slightly, and as you pass the pointer over each pie segment, you will see a tooltip giving the details of the data, including which car age range it refers to.
Tip The colors of a pie in a Power View map can be modified by selecting a different theme from the Power View menu. This can help the pie stand out against the map background.
If you have added data to the COLORS box of the Field List, and a legend is displayed, you can highlight segments of data in a map, much as you saw how to do in a chart in Chapter 5. This allows you to draw the audience’s attention to specific trends in your data.
- Using the map that you created and can see in Figure 8-7, click 6-10 (the second element) in the legend.
The segment of each pie corresponding to the car age range from 6-10 years will be highlighted, as you can see in Figure 8-8.
Figure 8-8. Highlighting map data
To remove highlighting from a map, all you have to do is click again on the legend element that you are using to highlight data, or simply click on the title of the legend.
Using a map as a basis for highlighting data follows the same logic that we saw in Chapter 5 for charts. That is, if you highlight a data element in a map, all other objects in the current report will be filtered by that selection.
For example, suppose that you have created the original map of net sales per country shown in Figure 8-6, which you have resized to allow for space to one side and have adjusted to show only European countries. You have also added a table composed simply of the Colour and NetSales fields. This report should look something like Figure 8-9, where you can see a bubble representing the sales figures for European countries where you have clients.
Figure 8-9. Sales in Europe before highlighting a specific country
Now, click on the data point for Switzerland. The map will highlight this country’s sales, and the table will also be updated to reflect the fact that you are, in effect, filtering out all data except for Switzerland. This report should now look like Figure 8-10.
Figure 8-10. Sales in Europe after highlighting Switzerland
Tip If you click on a segment of a pie for a country in a report where you have added a field for the Color element of a map as you did to create Figure 8-7, then you will be filtering on both elements that make up the segment—the country and the car age range.
Adjusting a Legend
If you are coming to maps in Power View after you have used charts, then you will feel a strong sense of déjà vu when modifying the map legend. Suppose that you want to move the legend from the right side of the map to the bottom of the map. Here is how:
- Revert to the map displayed in Figure 8-7.
- Click on the map to select it.
- In the Layout ribbon, click Legend.
- Select Show Legend At Bottom.
The map should now look like Figure 8-11.
Figure 8-11. Adjusting the position of the legend in a Power View map
The options available for positioning a legend are given in Table 8-2.
Table 8-2. Legend Placement Options for Power View Maps
|
Legend Option |
Description |
|---|---|
|
None |
The legend is removed. |
|
Show Legend At Right |
The legend is displayed on the right side the map (this is the default). |
|
Show Legend At Top |
The legend is displayed at the top of the map. |
|
Show Legend At Left |
The legend is displayed on the left side of the map. |
|
Show Legend At Bottom |
The legend is displayed at the bottom of the map. |
Another similarity between maps and charts or tables is their capacity for interactive filtering using tiles. Let’s suppose that you want to be able to see worldwide car sales by the color of car sold, as well as by car age range. Here is how:
- Take the map visualization that you created earlier for Figure 8-11.
- Drag the Colour field from the Colours table to the TILE BY box in the Layout section of the Field List. Alternatively you can click on the popup menu for the Colours field and select Add As Tile By.
Tiles will appear above the map, with the first tile element selected, as shown in Figure 8-12.
Figure 8-12. Adding tiles to a map
Tiles for maps act in exactly the same way as tiles for tables or charts. Clicking on a tile element will restrict the data in the map to the selected element. This means that the pie segments and the corresponding legend will only display available data. When applied to a map, this has the added effect of swapping the mapped region to show only the geographical areas that have data corresponding to the selected tile. To see this effect, try clicking on the tiles:
- Green
- British Racing Green
- Black
You will see the map “jump” to reflect the selected data. Since we are in the realm of geography, you could also try using the CountryName field to tile the map. This way, when you select a country, there is a real visual effect as the map moves to display the selected country.
In all other respects the tiles will act in the same way as was described in Chapter 6. So if this is your first experience of tiles, I suggest that you refer to the section on tiles in that chapter for further details.
Multiple Maps
Maps can also be displayed as multiples or “trellis” visualizations, just as charts can. To see this in action, let’s return to the map of car sales by car age range, as shown in Figure 8-11 earlier, and see how to display multiple maps—one per car color sold.
- Open the map visualization that you created for Figure 8-11.
- Drag the Colour field from the Colours table to the VERTICAL MULTIPLES box in the Layout section of the Field List. Alternatively you can click on the popup menu for the Colours field and select Add To Vertical Multiples.
The map will be split into multiple maps, as shown in Figure 8-13. You will note that each individual map has been resized and repositioned automatically to display only data for the relevant geographical area.
Figure 8-13. Multiple map visualization
Multiples, like tiles, behave in exactly the same way for maps as for charts. So, once again, I will not be repeating all the techniques and tricks here; instead I’ll refer you to the appropriate sections of Chapter 5 for full details. The only new point that is probably worth taking into consideration is that zooming and panning is now possible for each individual map.
One interesting trick when working with multiple maps is to use the same geographical data that is used as the location as the multiple. This is a purely visual effect, but it allows you to see the data per region (country, in this example), while comparing sales per region using the size of the data point.
Here is how you can do this:
- Open the map visualization that you created for Figure 8-11 earlier.
- Drag the CountryName field from the Countries table to the VERTICAL MULTIPLES box in the Layout section of the Field List. Alternatively you can click on the popup menu for the CountryName field and select Add To Vertical Multiples.
- If required, zoom in a little for certain individual maps so that the country fills the available space in the grid.
The map will be split, once again, into multiple maps, as shown in Figure 8-14. This time you can see the country map as a background for the country data. You will also note that the pie charts are proportional to the sales for each country. The only drawback when using this trick is that the title will repeat the field name that you applied to both the COLOR box and the VERTICAL MULTIPLES box. However, you can always remove the title.
Figure 8-14. Multiple maps defined by geography
Another extremely interesting interactive aspect of using Power View maps is the ability to drill down into a map to see further levels of detail. This will require, inevitably, source data that contains a geographical hierarchy. Fortunately we have hierarchical data available in the form of country and town, even if these are not specifically defined as a hierarchy in the underlying dataset.
To create a drill-down map, you need to follow the following steps:
- Insert a new Power View sheet.
- Create a table based on the following fields.
- ClientCountry from the Clients table
- NetSales from the SalesData table
- Convert the table to a map by clicking on the Map button in the Design ribbon.
- Resize the map to improve visibility.
- Pan and zoom to place the United Kingdom in the center of the map. You should see something like the map given in Figure 8-15.
Figure 8-15. An initial map ready for drill down
- Add the Town field from the Clients table to the LOCATIONS box under the ClientCountry field. The map should remain the same, as shown in Figure 8-15.
- Double-click on one of the bubbles for a country. I suggest the data for the United Kingdom in this example. The map will drill down to show the data for each town where sales occurred.
- Adjust the map display to show only the area where there is data for a town. You can see this in Figure 8-16.
Figure 8-16. Drill-down for the United Kingdom
The map shown in Figure 8-15 has a drill-up icon on the top right, just like a chart or matrix with drill-down enabled. You can see this icon if you place the mouse pointer over the map. Clicking on this will drill back up to the previous level. You can create as many levels of drill-down as your data allows.
Conclusion
Few things add the wow factor to a presentation in the way that a carefully crafted interactive map can. In this chapter, you saw how to add a map to a Power View report and then how to apply the techniques that you learned for other types of visualization to filter and highlight data in maps.
We then explored how to use a Power View map interactively to highlight data in other visualizations in the report. Finally, we saw how you can drill down into map data just as you can with matrices and charts.
Remember also that you can manipulate maps just like any other Power View element. Consequently, you can superimpose a chart on a map. Indeed, you can add slicers, as well, to add to the interactivity. Finally, you can enhance the map with text boxes and pictures to underscore the points that you are making about the data the map contains.
So, used judiciously, maps can become a vital part of your analytical and presentation toolkit. Have fun creating geographical representations of your data, and I hope that your audience is suitably impressed with your newfound skills.