17 Aug 2015

Dashboard for Messi´s worldrecord of 91 goals in one year with Excel + PowerView

#41 Dashboard for Messi´s worldrecord of 91 goals in one year with Excel + PowerView

Note 9/12/2016: 
See for a new version (with also new charts) made with Power BI, my new post: 

Next week starts the Spanish soccer League and I thought that it would be a nice moment to make a Excel PowerView dashboard for the best player of Barcelona (and the world and of all times), Lionel (Leo) Messi, based on this dashboard I once saw:




This dashboard was made with BI-tool Tableau, at the moment that Messi broke Gerd Mueller's 40-year record "most goals scored by a single player in a calendar year", on 9/12/2012, which was until then 85 goals, see:


For my Excel-dashboard I used the Tableau data-set and added 5 records for the goals Messi scored in the rest of Dec. 2012, so Messi´s world-record is 91 goals (made with Barcelona and Argentina ('Seleccion'). Actually I made 2 versions of the dashboard:

v1: report has summary-data (e.g. total goals per competition) and detail-data (match-details like date, opponent-team etc) (see fig.1b)

v2: report only has summary-data, no detail-data (see fig.1a)

As always, the download-URL´s for these Excel files can be found at the bottom of the blog-post.

            fig.1a PowerView report-v2 in PDF (exported from Excel) with Messi´s world record

           fig.1b PowerView report-v1 in PDF (exported from Excel) with Messi´s world record

To make this dashboard in Exel I used PowerView,  a (free) add-in for Excel 2013 Pro Plus, a Business Intelligence (BI)-tool for ad-hoc reporting. I copied the (Spanish) data-set with all Messi´s goals in 2012  from the Tableau-doc. to a worksheet  in Excel (and I added English comments to the Spanish column-headers), see fig.2

     fig.2: data-table with all Messi´s goals in 2012 (in pic. filtered for 1 opponent-team)

 Then I added this data to the datamodel in PowerPivot, another Excel add-in to perform data analysis using e.g. DAX-formulas like this:

Suma de Goles:=SUM([Goles])

, see fig.3 (formula-bar).

      fig.3: datamodel in PowerPivot, with DAX-formula

For sorting the goals by month ('Mes') in 'time-order' (so not in alphabetic-order), I added in the datamodel a column 'MesId' (1= Jan., 2= Feb etc), and used this DAX-formulas:

mes: =FORMAT([Fecha2];"MMM")

mesdID =MONTH([Fecha2])

see fig.4

        fig.4: datamodel with calculated field for month and month-ID, for sorting in report.

Once created the data-model in Power Pivot, I  opened Power View and created the elements in the report, which are (from top to bottom, left to right):

  1. pie-chart: goals per score-mode (left-foot, penalty etc)
  2. stacked bar-chart: goals per month and score-mode
  3. tile (filter): goals per team
  4. total goals, 'Suma de Goles', calculated field from the datamodel
  5. total matches, calculated field from the datamodel with DAX-formula: Total Matches:=DISTINCTCOUNT([Fecha]) (fecha = 'date'). NB: field´s value is # matches in which Messi scored = 46, so not all matches which Messi played in 2012 = 69.
  6. slicer (filter): competitions
  7. pivot-table, goals per match (with match-details like date, score-mode etc)
So in report-v1 you can see the 3 ways in which you can filter data in PowerView: in the Filter-View pane (to the right of the report-canvas), with a Slicer or with Tiles.

 as V1, except for bottom report, which contains a table in matrix-format and chart with goals per team and competition. NB: this matrix is like a pivot-table in Excel, see fig.5.

     fig.5: Pivot-table and chart : goals per team and competition

Creating the report-elements is just a matter of dragging and dropping the columns of the datamodel on the report-canvas and selecting the 'presentation-mode', e.g. Table or Chart (the matrix-table and chart in report-v2 are created in the same way (have the same fields), only the visualization-mode is different.
Note that the report-elements are connected: if you  use in report-v1 the Slicer to filter the competition 'Champions League' (CL), all report-elements only show the goal-totals of the CL-competition, see fig.6.

     fig.6: Slicer, Competition = 'Champions League' (CL)

Or if you filter in the chart with the goals per month = Dec., all report elements are filtered for Dec., see fig.7.

   fig.7: Filter in bar-chart Goals per month = Dec.

Or if you use the filter in the filter-pane and filter on an opponent team, e.g. "Rayo Vallecano", all report-elements only show goals Messi made against this team (Rayo), see fig.8.

     fig.8: Filter in filter-view pane, opponent-team = Rayo.

When I finished the report, I exported (published) it to a PDF-file (also included in downloads below).

So with this post I wanted to show you the reporting-possibilities of Excel and PowerView. For another example, see my post about the Sochi Winter Games:


Interesting Links
On my research for this post I found some interesting links, including videos with the 91 goals from Messi, from which you should see at least one of the first 2. Or if you don´t have so much time, watch only the 3d video, with maybe Messi´s best goal ever, which he made when he was only 19.

Videos Messi:

Lionel Messi - All 91 Goals in 2012 with Commentary

Los 91 Goles De Leo Messi En 2012

Lionel Messi Goal vs Getafe

https://youtu.be/8o_Lionel Messi - EL D10S - Short Movie |HD

Lionel Messi - The Magician - 2015 ● Skills ,Goals ,Dribbles , Assists |HD

Infographics Messi´s 91 goals:

*1 positions field from where Messi scored

Messi vs Mueller


 Statistics career Messi with Barcelona


Business Intelligence with Excel and Powerview

Getting Started With Power View In Excel 2013 

Creating KPI dashboard using Excel 2013 PowerPivot

From Data to Insight & Impact: Building a Sales Summary with Power View

Excel 2013 Power BI Tools Part 17 - Filters in Power View


#Mirror 1: Scribd.com (PDF file report-V2):


#Mirror 2: MS Onedrive (Excel file report-V2):
NB: this site has 'Excel-Online', so you can view my Excel-doc if you don´t have MS Excel on your PC


#Mirror 3: Google Drive (1 zip file with Excel and PDF files report V1 and V2):

1 Aug 2015

Month-calendar for August 2015 in Excel

#40 Month-calendar for August 2015 in Excel

In this post the new month-calendar for August 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is "La Vuelta Ciclista a EspaƱa - Tour of Spain 2015", which starts 20 August, for more info, see:


This cycling Grand Tour also passed through the mountains Picos de Europa (Asturia), a very nice part of Spain where I spent several holidays, for some photos, see:

https://www.flickr.com/photos/46731978@N00/7573362742/in/photolist-c: eqYW-cxereq-cxerEL-cxestW-cxesTG-cxes2m-cxetdw-cxetNb-cxeu37-cxeuvE/

In my calendar I used a clipart from:

-- fig.1

Downloads (Excel and PDF):