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