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

4/1/2021:
Today I made a new live Messi Goal Tracker dashboard, see:

https://worktimesheet2014.blogspot.com/2021/01/messi-goal-tracker-dashboard-in-google.html

Note 9/12/2016: 
See for a new version (with also new charts) made with Power BI, my new post: 
http://worktimesheet2014.blogspot.com/2016/12/powerbi-dashboard-for-messis.html


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:

http://tblsft.com/public/gallery/messi039s-86-goals

or:

http://canchallena.lanacion.com.ar/1535469-gerd-muller-rompio-el-silencio-espero-que-su-record-dure-otros-40-anos

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:

http://www.guinnessworldrecords.com/news/2012/12/barcelona-star-lionel-messi-sets-new-goalscoring-record-46285/

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):

Report-V1:

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.


Report-V2:
 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:

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


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:

*1
Lionel Messi - All 91 Goals in 2012 with Commentary

*2
Los 91 Goles De Leo Messi En 2012

*3
Lionel Messi Goal vs Getafe

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

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


Infographics Messi´s 91 goals:

*1 positions field from where Messi scored
http://i.imgur.com/ewq4Y.jpg 
http://www.headingfortheexits.com/messi91/

*2
Messi vs Mueller

 *3
http://www.fcbarcelona.com/football/first-team/messi-91-goals-2012-world-record-infographic

 *4
 Statistics career Messi with Barcelona

*5
http://bleacherreport.com/articles/1456355-breaking-down-lionel-messis-magical-91-goal-year-for-barcelona-and-argentina


Business Intelligence with Excel and Powerview

*1
Getting Started With Power View In Excel 2013 

*2
Creating KPI dashboard using Excel 2013 PowerPivot

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

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


Downloads:

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

https://es.scribd.com/doc/274861437/GoalsMessi2012-v2-R1-PDF

#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

http://1drv.ms/1hiNhFP

#Mirror 3: Google Drive (1 zip file with Excel and PDF files report V1 and V2):
https://goo.gl/Ttkzmz