FIFA 2014 World Cup Data Analysis with Power BI

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

2405348_xbig-lnd

Fifa 2014 is not a hot topic these days, but it was the headline news June 2014. With new capabilities in Power BI Designer I tempted to use FIFA 2014 Worldcup data as a demo of it, and play with the tool to see how it works. Fortunately I was able to build some interesting dashboards with it. The demo work was interesting enough to say that I probably would write detailed information about how to build such demo, and would use it in some of my conference speakings.

I’ve used data from official Fifa.com website for most parts of this demo. you can reach FIFA.com official site for 2014 Brazil from here:

1270583_large-lnd

http://www.fifa.com/worldcup/archive/brazil2014/index.html

For those of you who might not be fan of Football; the end result was Germany won the cup. So I’ve tried to build some dashboards to not only show the overall statistics of the match, but also highlights how/why Germany won the cup. I started with data sets that has matches information for this world cup, and I’ve ended up having a Match analysis dashboard as below. the dashboard shows number of matches played by each team, stage of match (group stage, round of 16,Quarter-finals, semi-finals, or final), details of each match in a grid view, number of Wins, Losses, and Draws by every team.

match

The dashboard can show more meaningful result when you highlight a country. In following you see that If I highlight Germany, it will show the group that Germany raised from, all matches of Germany,  and Number of wins, losses, and draws of this country. as you see in screenshot below Germany had 6 wins and one draw. details of matches shows that the only one draw was against Ghana at the group stage.

match_h

Then I started building a dashboard for Goals scored in this tournament, I used the goal scorers data from FIFA.com website and combined that with team’s goal scored. as a result I’ve ended up with building a dashboard that shows top goal scorers for FIFA 2014, with number of matches they played. As an example you see James Rodriguez top scorer with less matches played compared with other top 6 scorers. I’m not a fan of pie chart, as it is not usually obvious understanding of it. However I found that number of goals scored by right foot, left foot, and head are so much obvious in the difference that pie chart show it clearly. and I’ve also showed the goal scored by each country, which as you see Germany, Netherlands, and Colombia are top three here.

score

I’ve also used some more team statistics data from FIFA.com website and built a dashboard for team attacks, defends, passes completed, and Win/Loss/Draw ratio. Dashboard below show a map view of teams/countries played in this tournament, a clustered bar chart of their win/loss/draw ratio. a column chart for their attacking, and a combo chart for their defense splitted by attempted clearance, and completed clearance. with some gauges that shows passes completed, number of goal assists, and solo run into the area. a pie chart also shows number of attempts on the target, off-target, and oh yea Woodwork!

dash

Above dashboard is my favorite one, as it can even highlights how/why Germany won the world cup. For winning in the world cup you should not only have an attacking team, you also need a great defense. Football is a match of 11 men, so number of solo runs won’t be a big deal. the best team is the team that do the most assists and the most completed passes. If I highlight Germany in the above chart we will see really interesting results. Germany was not the best attacking team, but it was the best defensive team with the most completed clearance. Germany had the most passes completed through the tournament, the most assists for goal scoring (13 assists which is fantastic), high rate of attempts on target. no wonder Germany won with such great numbers.

germany

Now let’s compare it with Argentina which lost the final game against Germany. Argentina have a good attacking team, with the best solo runs (especially because of Messi). However they don’t have a good result for their assists (only 3 assists in 7 matches), and their number of passes completed are much lower than Germany. Considering the fact that Argentina was also good defensive team, that attacking and defensive strategy helped them to come up to the final, but for winning the cup Argentina definitely needed better passing skills, and try to assist in the goals more.

argentina

 

A Word of Techy

It would be a very long post here if I explain how I did the technical side of this demo. I will write about it in next blog posts. I want to only mention few major aspects of it here in this post. Power BI Designer is the new Power BI editor tool which helps building dashboards and visualization, data transformation, and modelling. Power BI Designer has a combination of three Power BI Components which are:

  • Power Query
  • Power Pivot
  • Power View

With Power Query I’ve wrote queries to get data from FIFA.COM website

pq

Then I’ve created the relationship for modelling

pp

And finally I’ve made reports (which uses Power View component);

pv

After building the demo, simply I’ve uploaded the *.pbix file (Power BI Designer file) into PowerBI.com website. I’ve just simply built some dashboard for it;

pbi

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

15 thoughts on “FIFA 2014 World Cup Data Analysis with Power BI

Leave a Reply

%d bloggers like this: