Tips and Tricks: Mapping Data to the World in a PowerPivot Workbook

Last week I came across a stunning PowerPivot solution that visualizes research data on top of a world map, as illustrated in the following screenshot. What’s particularly stunning about this solution is that it works even in the browser despite the fact that Excel Services doesn’t support custom shapes or background images for country objects or maps. How did these guys manage to get the data onto this map? Out of sheer curiosity, I asked the creators and was happy to learn that it isn’t too complicated. The trick is to use a semi-transparent image in the foreground. All you need is Excel 2010 with PowerPivot and Excel Services with PowerPivot for SharePoint—and a Portable Network Graphics (PNG) file of a world map with perhaps 75% transparency.

The originator of the idea and project sponsor for the actual business solution here at Microsoft is Senior Business Development Manager Hicki Okamoto. Jeremiah Hansen, BI Consultant at Slalom Consulting, implemented it. Jeremiah also helped me to recreate the solution based on publicly available sample data by sharing his solution files with me. Thanks again, Hicki and Jeremiah!

This article’s sample solution relies on a Contoso Electronics workbook, created by following the tutorial “Create Your First PowerPivot Workbook” in the SQL Server PowerPivot for Microsoft Excel Help file. Just press F1 in PowerPivot to display the help information. It is not necessary to complete the entire tutorial. Stop after creating a calculated column for total profit. The finished workbook (50 MB) is also attached to this blog post.

The first step in building the Contoso Electronics dashboard is nothing new or special for PowerPivot users. Just add a PivotTable on a new worksheet. You might also want to hide the tutorial’s Stores worksheet (right-click the worksheet tab and click Hide) so that your workbook only shows Sheet1 with an empty PivotTable1.

Now, for the Contoso Electronics dashboard, let’s display the total sales amount in millions of dollars and the corresponding year-over-year growth (abbreviated as YoY Δ) for specific geographic regions. If you need a primer on calculating year-over-year growth, read Howie’s excellent article Time Intelligence Functions in DAX here at our team blog site. Howie calculates year-over-year growth for Contoso’s Store Sales. My approach is only slightly different to calculate year-over-year growth for all sales (not just Store Sales). Add the following measures to the FactSales table:

With the required measures in place, select PivotTable1 on Sheet1 and drag the following fields to the field areas in the PowerPivot Field List (see also the following screenshot):

The next task is to rearrange the cells according to their geographic location on the map. As a prerequisite to this step, you must convert the PivotTable to individual formulas similar to the procedures described in the blog post Creating a Stock Ticker View for Performance Data in PowerPivot. It’s straightforward: With the PivotTable selected, switch to the Options ribbon, and then under OLAP Tools, select Convert to Formulas. Delete all rows that you don’t want to place on the map.

Follow these steps to finish the work on the data cells:

1. Select the YoY Δ data cells, right-click the selection, select Format Cells, and then on the Number tab under Category select Percentage and click OK.
2. Select the YoY Δ header cell and copy the formula without the equal sign to the clipboard, that is CUBEMEMBER("PowerPivot Data","[Measures].[YoY Δ]").
3. Select the first YoY Δ data cell and replace the cell reference for the YoY Δ header cell in the formula (such as D$11) with the expression you copied to the clipboard in the previous step. Verify that the value of the data cell remains the same when you apply the changes.
4. Double-click the lower right corner of the selected data cell to apply the formula changes to all remaining YoY Δ data cells.
5. Delete the YoY Δ header cell and verify that the YoY Δ values remain unaffected.
6. Repeat this procedure for the TotalSalesInMillions column.

The growth indicators are still missing. Follow these steps to add them:

1. Select the cell to the right of the first YoY Δ data cell, then type an equal sign (=) into the formula box, select the YoY Δ data cell, and then press Enter. Verify that the cell has the same value as its neighbor.
2. Select the cell again and then double-click the lower right corner to apply the formula changes to all remaining YoY Δ data cells.
3. Select all newly created cells, and then on the Home ribbon, under Conditional Formatting, select New Rule.
4. In the New Formatting Rule, under Format Style, select Icon Sets.
5. Under Icon Style, select the style Five Icons Colored.
6. For all icons, change the Type to Number, and then specify a desired value for each icon, such as 0.15, 0.05, -0.05, and -0.15.
7. Select the checkbox Show Icon Only and then click OK twice.
8. In the FiscalYear slicer, click on 2008. Verify that the growth indicators work, as the following screenshot illustrates. Note that YoY Δ values are only available for the fiscal years 2008 and 2009 in the data set.

This concludes the PowerPivot work. Next comes the creative part. Let’s add the map and move the cells to their correct locations:

1. Move the slicers to the very bottom of the worksheet for now.
2. Switch to the Paige Layout tab and then click on Background.
3. In the Sheet Background dialog box, select the .png file of your world map, and then click Open.
4. Using the world map as a background image on the worksheet helps moving the cells to the right locations. Adjust font styles and sizes, merge cells, and align text as necessary.

The dashboard isn’t finished yet. As mentioned earlier, Excel Services does not support background images on worksheets, so you must remove the background again and insert the semi-transparent image in the foreground. Follow these steps:

1. On the Paige Layout tab, click on Delete Background.
2. Select cell A1, switch to the Insert tab, and then click Picture.
3. In the Insert Picture dialog box, select the .png file of your world map, and then click Insert.
4. Resize the image to match the locations of your data cells.
5. Right-click the header of row 1 and click Insert. Repeat this step to insert 5 new rows at the top of the worksheet. Note that map and data cells move down together.
6. Enter a dashboard title and other useful information in the first row (see screenshot).
7. Right-click the image and select Send to Back so that the image is behind the slicer objects.
8. Move the slicers back to the top and resize them to make use of the available space.
9. Apply header formatting, such as background and borders, as desired.
10. Right-click Sheet1 at the bottom, click Rename, and then type World and press Enter.
11. Open the File menu, click Options, select Advanced, and then clear the checkboxes Show Row and Column Headers and Show Gridlines.
12. Click OK and save your work.

You can now upload the workbook to SharePoint and interact with it in a Web browser (see the screenshot earlier in this article). Of course, you can also add the solution in an Excel Web Access Web Part to a dashboard page. In the following screenshot, I added the worksheet to the homepage of a PowerPivot site. Never cease to be amazed at the versatility of Excel Services and PowerPivot!

In a follow-up blog post, I’m planning to show you how to convert this dashboard from a PowerPivot workbook to a tabular BI Semantic Model in order to support larger data sets, row-level security, and other cool features. Stay tuned!