Published Date : July 26, 2015
It was sometimes ago that I’ve posted some data analysis that I’ve done with Power BI Designer on FIFA 2014 Worldcup results. In this post I want to take you through the process of how to achieve that analysis. This post is an introduction to Power BI features with the example of FIFA 2014 Worldcup result set. Through this post you will learn how to connect to web based data sets and mash up the data with Power Query component of Power BI Desktop. And finally how to model it and visualize it in the Power BI report. Most of the data for this example comes from data sets in FIFA 2014 Worldcup official website: http://www.fifa.com/worldcup/archive/brazil2014/index.html
For this example I use Power BI Desktop which has been announced yesterday as General Availability, you can download Power BI Desktop (32 or 64 bit) from link below:
http://www.microsoft.com/en-us/download/details.aspx?id=45331
I use more than one data set for analysis, so let’s start with some basic data sets;
Number of goals scored by each team listed in this URL: http://www.fifa.com/worldcup/archive/brazil2014/statistics/teams/goal-scored.html
Steps to get this data set into Power BI is;
Query Editor is the Power Query editor window, which gives you ability to transform data and then load it into the model that Power BI can use. There are different panes in Power Query Editor window here;
This data set is almost ready to use, the only transformation I want to make here is to remove extra two characters at the end of each column name that came from icons used in the FIFA website. so rename every column with right click on that and choosing “rename column” option from the menu.
Also rename the Query name to TeamGoals in the right hand side pane in Query Setting section. Here is the result set after above changes;
The second data set is passed by teams which we get from this url: http://www.fifa.com/worldcup/archive/brazil2014/statistics/teams/passes.html
I would also like to add another data set to this analysis which is Teams Defending result set. URL: http://www.fifa.com/worldcup/archive/brazil2014/statistics/teams/defending.html
We can add much more data sets from FIFA official website. but for this example let’s keep this simple and stick to three data sets above. In this section we would model relationship between these data sets, and model it to be used in reporting section.
in Query Editor window click on Close & Load icon in the Home tab, under Close section
In the Power BI Desktop from left pane click on Relationships tab and you will see the relationship created itself by the similarity of column name between queries.
You can also change the relationship if you want. current relationship is based on Teams column as the key column.
I’ll keep the relationship as it is.
In the data tab you can view the data set, and set formatting of the columns or add new measures (DAX measures) if you want. Screenshot below shows how to access data tab from the left pane. You can also see the Modeling menu that gives you ability to create and edit measures, as well as format them in appropriate way. you can also access managing relationships from this menu.
Let’s set formatting of percentage columns appropriate;
Now our model is ready enough to be used in reporting section
Now let’s make some visualization with the Report tab based on the data in model we’ve built so far.
From the Fields section under TeamGoals, drag and drop teams into the main pane, then drag and drop Goals Scored on it. you will have a table showing values as below
Now from Visualizations click on Column chart, and you will see the result set in the chart easily.
Let’s highlight teams that scored their goals in open play, rather than penalty or own goals. We can achieve that with the fantastic new feature of Color Saturation in the Power BI as mentioned below
The result set als
o can be modified with some color them for background, and moving title to the center (all of these can be achieved in format section of the visualization)
Double click on the map from visualization section. Then from TeamPasses Drag and drop Teams into the Location section of the map setting. Bring Passes Completed into the values section
Let’s change the color, and color saturate it by number of matches each team played.
Now let’s create a gauge for goals scored. set the value as Goals Scored. Minimum value as Min of Goals Scored, Maximum value as Max of Goals scored, and the Target as Average of Goals Scored.
as the last visualization item in this report I would like to show a column chart combined with line chart. the column values shows Completed Clearance from TeamDefendings, and the line value shows number of Saves. Here is the chart
Here is our completed report in the Power BI Desktop
Now let’s publish this report into Power BI website. for this section you have to create an account in Power BI website, if you don’t have it, just create it, it is free!
Click on Publish option in the menu above, and your report simply will be uploaded into your Power BI account.
Open the report in Web
You can even pin some visualization items as dashboards as illustrated below
Pinned dashboards can then be viewed separately in dashboards section
Published Date : April 16, 2015
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:
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.
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.
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.
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!
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.
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.
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:
With Power Query I’ve wrote queries to get data from FIFA.COM website
Then I’ve created the relationship for modelling
And finally I’ve made reports (which uses Power View component);
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;
