4/1/2021:
Today I made a new live Messi Goal Tracker dashboard, see:
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
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
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
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:
- pie-chart: goals per score-mode (left-foot, penalty etc)
- stacked bar-chart: goals per month and score-mode
- tile (filter): goals per team
- total goals, 'Suma de Goles', calculated field from the datamodel
- 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.
- slicer (filter): competitions
- pivot-table, goals per match (with match-details like date, score-mode etc)
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.
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.
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
