Excel 2013, PowerMap and World Cup Soccer

#15  Excel 2013, PowerMap and 2014 World Cup Soccer 

NB: This post has 2 Excel-files (embedded in 1 zip), see ‘Downloads’ at bottom of page. To be able to open these files, you must have Excel 2013 with the Power-BI add-ins installed.

With the 2014 FIFA Worldcup coming closer, I thought it might be interesting to use this event to show the Excel add-in PowerMap,  a component of the Power-BI suite for Excel 2013 which I didn´t show yet in my previous post about Excel and Business Intelligence (BI), see:

http://worktimesheet2014.blogspot.com.es/2014/05/excel-2013-and-business-intelligence.html

As I explained in part 2 of that post, in Excel 2013 you can use PowerView to visualize geospatial data in a map. But with PowerMap you can do much more then that, as you´ll see in this post.

PART 1:

Excel: WorldCupHistory.xlsx

First a little bit of history of the World Cup Soccer. In this Excel, you can see which teams reached the top four (how often did the team end at position 1 to 4) and their final ranking in the tournament (so 1 to 4), for which I used PowerQuery with option 'select from Web', to import the table with this data from:

http://en.wikipedia.org/wiki/FIFA_World_Cup

Then I edited this table a bit, to end up with the table in PowerPivot as you can see in fig.1  (note the column 'Continent' has corresponding datacategory).

fig.1 table in PowerPivot

Then I created in a new sheet in Excel a PowerView report with a map and the winners of all World Cups (so the ones who got the title 'World Campion'), and how often they won, which determines the size of the dot on the map, see fig.2. Note that in Filter 'Titles' value '0' is un-checked (and values '1' to '5' checked), so that the 2 elements in the report (map and table) only show countries which have won 1 or more times the World Cup.

fig.2: PowerView with map

And the last step was to create a simular report with PowerMap, see fig.3.

fig.3: PowerMap

For this report, I chose the options 'Globe (there is also a 'flat-map' variant, see part 2 of this post) and 'bar-charts' (other options are 'dots' or 'regions', see part 2). As you can see in the bottom-left corner, the Bing-Map is used for this, so you must be connected to the web.
NB: the PowerMap-report is stored in Excel in sheet-3, although you don´t directly see it. First, you must activate the COM-addin for PowerMap, see menu File > Options), and then in sheet-3 you must click in menu Insert > PowerMap, and then apears the 'tour' which I created). PowerMap is still in 'preview-version', I suppose Microsoft solves this before the final release.


PART 2:

Excel: WorldCup2014Countries.xlsx

This Excel has a table with the 32 countries which qualified for the 2014 World Cup in Brazil, see e.g.:

http://prosoccertalk.nbcsports.com/2013/11/20/2014-fifa-world-cup-countries-qualified-for-teams-draw-date/

Note that this table is what they call in BI-terms a 'factless fact-table', that is: it has only dimension-columns (type char) and no measures (type number). I could have added a dummy-measure 'counter' which always has value '1', but it is not necessary.
In fig.4 and 5 you can see the reports in PowerView and PowerMap, the last one with options 'Region' (to color the 32 countries) and 'flat map', and if you change this to 'Globe', it shows a nice animated transition from Map to Globe.

fig.4: PowerView

fig.5: PowerMap

For more information about PowerMap, see e.g.:

http://www.databasejournal.com/sqletc/getting-started-with-microsoft-power-map-for-excel.html


Downloads

https://drive.google.com/file/d/0BywxxSJoaUYxdF9HYVAtZ3pLcWs/edit?usp=sharing