Information, tips, news and announcements about SQL Server Analysis Services and PowerPivot directly from the product team.
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:
=CONCATENATE("$", CONCATENATE(FORMAT(CALCULATE(ROUND(SUM (FactSales[SalesAmount])/1000000, 0)) , "#,###"), "M"))
Creates a string that encloses the sales amount in a dollar sign and an ‘M’ for millions. The value is rounded to a million.
=CALCULATE(SUM (FactSales[SalesAmount]), DATEADD(DimDate[DateKey], -1, YEAR))
Calculates the sales amount for the previous year.
=IF(SUM(FactSales[SalesAmount]),(IF ([SalesPrevYr],(SUM (FactSales[SalesAmount]) - [SalesPrevYr])/[SalesPrevYr], BLANK())),BLANK())
Calculates the year-over-year growth if the current year and the previous year have sales values. The formula returns BLANK() if the previous or the current year has no value.
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):
TotalSalesInMillions, YoY Δ
Fiscal Year, ChannelName, ProductCategoryName
DimDate, DimChannel, ProductCategory
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:
The growth indicators are still missing. Follow these steps to add them:
This concludes the PowerPivot work. Next comes the creative part. Let’s add the map and move the cells to their correct locations:
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:
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!
I love the attached sample!
It seem if I save the map to sharepoint site, when view the map it will show a erro about update
Thanks a lot and really very nice right up
Syed Qazafi Anjum
When I publish it to SharePoint, the image is not transparent anymore, I cannot see the data, but it works nice in Excel itself. Thanks