Transfers and remaining games

Over the last few months I have been creating Fantasy Football data flows using Azure which allows me to automate the data flows. This has enabled me to start looking at what data is important. I’ve been concentrating on the Transfers just lately. So here is a Power BI dashboard which shows almost real time data (Data captured every 15 minutes)

In this dashboard I have the ability to look at various pieces of data:

  • Transfer data. This allows me to see how big a player is expected to be in the coming game week.
  • Goals and assists for those players. I can select the game weeks so I can select what I believe is the right amount of weeks to go back over, 3 to 6 appears to be the ‘good zone’.
  • Remaining games details.


The actual Transfer section starts off at the team level then you drill down to players.

I love the capabilities of Power BI to drill down like this, it really gives you some options.



The whole dashboard becomes the selection options. If I actually select Teams or Positions only the data which meets that criteria will be shown, if I select something on the charts the rest stays there but looks opaque. Here I have selected Harry Kane as he wasn’t high up the charts for scoring/Assists.

The final part allows me to see the fixtures. Now this could be an interesting selection to highlight. Over the years I have observed that the first 6 and last 6 games of the season throws up the biggest surprises. Take this season, West Brom have beaten Man united and drew with Liverpool in the last few weeks, and they are in the relegation zone.  Spurs have West Brom so I suspect that the players who are bringing in Harry Kane expect a big return, but could there be surprise.

I still have a lot of data to blend to get me to where I want to be, but I’m building up some good history, this at least gives me a good place to start.

Having the historical data allows me to change calculations and then apply the new settings to see what happens.

Roll on next season 🙂




Latest dashboards

Been playing again with Power BI, the latest dashboards shows various stats to assist with my player picking:

Points & Average points by teams and positions  over a given time period.

Points & Average points by players over a given time period.

Points & Average points by players outside the top 6 teams  over a given time period.

Goals scored & Assists by teams and positions  over a given time period.

Goals scored & Assists by players  outside the top 6 teams  over a given time period.

If I drill down on the assists to show players you can see Benteke had 4 assists prior to last weeks games, who’d have thought that 🙂


Goalkeepers stats.

The purpose of these dashboards is to show what is happening and to give you the ability to ‘drill down’ from teams to players. Need to start looking at the next game week and think about game week 31 as that is a reduced game week in terms of games.


Home Team dashboard

I’ve spent more time playing on dashboards, it appears the more time I spend the more options I want to include :). I decided to go with a Home Team dashboard.

You pick a League and a season and it gives you the home info.

If you select a Team you get the details

If we look at the games in details we can see that home draws are going to cost Liverpool.

You can look at the data across multiple season as well, here I selected 3 seasons and it gave me this data.

I’ve built quite a few dashboards now, I need to build the away dashboard next then I’m onto the predictive analysis stuff. I will then start to use R/Python to really push the data. The current Predictions stuff is not accurate enough yet so I’m busy building in some other variables.

Its amazing what you can add in and luckily with some of the variables I can retest all the previous results to see what effect it has. Some of the variables I cant use as I only have certain data points but going forward that will grow.

During my time on this side project it has made me realise the importance of collecting data, but not just collecting it but actually using it.



Working on the Prediction dashbord

A new dashboard has been added, this time it based on real football and it includes Predictions 🙂

This one allows you to select a home team and an away team and you can see stats based on that fixture. In the example I selected ‘Manchester City’ and ‘Newcastle’. Looking at the stats its going to be a home win.


It has also been predicted as a home win 67.74%. The predictions are still being tested as I have changed the algorithm a lot this season.

The stats in the tiles are based on historical fixtures between the 2 teams. Currently I’m using all data but I will modify this once I see how relevant all the data is.

If we change the teams to Arsenal/Palace we can see at one point this would have been a guaranteed Home win. But recent form shows a different story. The Prediction is 60.78% for a home win but I think it might be a draw.

if we use the players dashboard we can see it might be closer.

After this weeks games I will be doing some more analysis before I update all the data.

Watch out for another post coming out very soon.

More analysis on players using Power BI – upto and including week 9

So in my quest to see if I can use stats to improve my Fantasy league team I have come up with a few more dashboards.

Team and Players

The first dashboard shows me the overall points for the teams, then the weekly points for the teams and then the players. Ignore the up/down as that is based on limited data, it will become more important as the weeks go by.

By selecting the team it will filter all the charts so you can see what each player got.

As well as the dashboards based on overall points etc. I wanted to see if I could create a ‘weighting’ which could be applied and then you can see if a player is worth buying, hence the Bargain column. It will be interesting to see the stats after I have uploaded this weeks data.



I will be updating the data and posting the latest stats later.


Small project start to finish – Footy data

So far I have been focused on showing you the charts that I created, here I am going to build a small project from the start. The goal is to produce  visualization that shows me FTHG against FTAG so I can see at a glance which teams will do well.

Starting off – Data

Getting the data right is the basis for a trouble free Power BI experience, as I’m using SQL Server it makes the job easier. You can data wrangle in Power BI but I prefer to do as much as possible before. My data model is made up of a single Fact table and various Dimension tables. The Fact is the results etc. and the Dimensions are the Teams, Season, Leagues etc. For the purpose of this I have not used Fact or Dim as either a Schema or in the naming convention, normally I would 🙂


Note: The Team table I have turned into 2 views as I want to select Home and Away teams. There are other ways of doing this but this is a simple way.


Power BI

Within Power BI I import the Tables and Views I require, and check the relationships between them. All the lines are solid so I’m happy.


The Dashboard is simply 2 charts and a selector. The selector is for the Leagues. The charts are a stacked bar chart and a quadrant chart. The stacked bar is based on Goal Difference. I created a new measure for this.

The Quadrant is based on FTHG against FTAG.

Now, you can select either of the charts and it will filter the other. Here I selected a specific value on the Stacked bar, Aston Villa -21 and it filtered the Quadrant chart.


If you hover over the Quadrant bubble for Aston Villa you can see FTHG 14 and FTAG 35 which gives you -21



This is a very simple project with just one dashboard, but because of the data even though it is simple we could produce quite a lot of charts.



Its been an Age

Its been an age since my last post. Saying that I have been busy with work and things and the things have included re-development of the website and backend.

I have also been playing with Power BI a lot more. Here are some of my latest dashboards.

This chart is showing Home and Away goal difference by the current season.


This chart is showing Home and Away goal difference by various season.

This chart is showing home and away wins by teams by season, it also shows the percentage by season and it compares with the previous season to see if there has been more home wins or away wins.



It becomes more interesting when you look over more than 2 seasons.


I will be getting more active on here so keep an eye out for more posts.


Even more Power BI charts.

Another week and another Power BI chart, I’m looking forward to when we get all this good stuff in Azure.

This chart is looking at average points either at home or away for a given month and the selected teams, its all based on historical data since 2010.


There is so much more that this chart will do but I thought I would share it with you.


Power BI – Predictions

Still playing with Power BI, producing some charts for the Predictions.

This chart shows all predictions, for the test I have used 2 weeks worth of predictions.


You can select either all, not the latest or the latest predictions.



Then by selecting a game and hovering over the bubble you can see the stats, Arsenal home win against Watford, currently 0-2 down.





Power BI – Form of teams

Still playing with Power BI and looking at the data from the Predictor database.

This chart shows home and away form, the selectors on the left are for selecting League and teams


If you click the charts the data will change. I have selected the Liverpool loss at home and the away charts shows you it was Swansea.



If I select the Liverpool away losses on the away form chart, it highlights the teams that won on the home chart.



If I use the slicers and once again select Liverpool, the home chart shows Liverpool only and the away chart shows the teams and the results.



The use of Power BI makes the charts very interactive. As I’m looking at form charts I’m listening to Liverpool who are currently getting beat 1-2 at home to Wolves. January has been a bad month for Liverpool.