Building the English Premier League Table with Power BI
Вставка
- Опубліковано 23 лип 2024
- Starting with the match record of this season's English Premier League games, we'll build a league table. To do this, we'll use the Query Editor to split, reshape, sort, append, calculate on then group the data. We'll build our table visualisation, then improve it with conditional formatting and custom tooltips.
The datasets and course materials are in at github.com/MarkWilcock/lbag-o...
Music: www.bensound.com
Hello, i did this model for Qatar 2022 at my office and everyone were really excited for it, now as American Cup is coming I was requested to do another one but with American Cup design.
So thank you very much for this video, it helped me a lot wit Power BI.
Great video. I learned the basics of lot of things which I can now apply to my player stats visualizations! Thank you!
Thank you this was very helpful 👌
Thanks for the video mark, good reminder of the live event to come back to when needed. Paul
Very welcome
What an excepcional video. Thank you so much for sharing your knowledge!
My pleasure! Thanks Rafael.
Man! What a life saver! Amazing work mate! Really appreciate it. Was struggling here for a couple of days on how to do exactly that. Legend!!!
Hi Laio, Glad you enjoyed it. - Mark
Great video. Thank you, Mark.
Glad you enjoyed it
Lo mejor que vi HOY y en AÑOS. Congrats!
Muchas Gracias
@@LondonBusinessAnalyticsGroup Sería bueno incluir un Rank by Points DG en GF
learn a ton from it!! thx
many thanks for the video - very insightful. Looking forward to the next videos.
HI Nara, Thanks for watching! - Mark
Hi Nara, These links may be useful.
EPL Football Resources and Links
League Tables
www.bbc.co.uk/sport/football/tables
www.premierleague.com/tables
EPL Match Data from
www.football-data.co.uk/englandm.php
www.premierleague.com/results
www.bbc.co.uk/sport/football/premier-league/scores-fixtures/2019-09
Other EPL stats available at footystats.org/england/premier-league
great content,loving it!
Glad you enjoy it!
Amazing!
Excellent content
Thanks for the great video!!! Can you briefly describe here how to proceed so that I can still see/recognize which team played home and away in the Tooltip page? Thank you and good luck!!!!!!
Please do kindly inform how do we add dropdown filter based on the matches have been played?
Great job!
I did it too for the Italian Calcio Serie A. Team Stats, Player Stats and also with updated results of season 20-21.
I can share the link.
Best!
Thanks Jefferson, please do share the link. - Mark
Jefferson Ramos Where did You get data for Seria A?Can you send me that so i can practice? Where do you put your projects?Thanks in advance
Good video. Some great tips. I was looking for how you would also add the ranking of each team in the table?
Hi A J, In short, use the DAX RANKX() function, or sort the teams in the query editor then add an Index column. We will be covering ranking in a future video. - Mark
Very informative. Looking forward to the next videos in this series. Is it possible to show how someone can add the past 5 results to the main League table and display as it appears on the BBC page (ie WWWLW) rather than via a tooltip?
Hi Steve, We're going to do this on the LBAG sessions on 1st & 2nd June probably (and also in SQL, R and DAX) and we'll have a video out a few days after that. It's a harder challenge, we have to add an index to list *each* team in reverse date order, filter that less than or equal to 5 for the last 5 matches, pivot the remaining rows then append the resultant 5 columns. And we can't use the Query Editor UI for all of it. So too much to do in the session last week but we are devoting another session to it soon. - Mark
Well done! Thanks for sharing your solution ;)
I was also trying to make a report of the Premier League, but I couldn't figure out how to model the data.
I made a separate team table with one HomeStats table and an AwayStats table. It went well until I wanted to plot an overview of all the matches with.
Do you have an idea how to make a total overview of the played matches? Not like you did with an opponent column, but with an home and away team.
Hi Aslan, to summarise over two separate tables, perhaps use a DAX measure to do this? - Mark
@@LondonBusinessAnalyticsGroup Where is the raw data , Sir? How we pull it ? Thanks
I am trying the same thing but I am trying to figure out how I can model the data so that using a slicer, you can select a team and it brings in both home and away data for the selected team. My results data has a home team column and an away team column. But I can only do the slicer on either one of those fields. Then when filtering, it only filters on the field I used in the slicer. for example, I want to be able to select a team and return both home and away results for them
amazing content thank you, have the other sessions started yet?
Hi Keegan, we post details of our upcoming live sessions on our meetup page at www.meetup.com/London-Business-Analytics-Group/- Mark
Great video for learning Power BI.
I followed your video and decided to.do.again but with the 2020 rugby league season as I am more a fan of rugby league.
Anyway I came across an issue and couldnt seem to work out. Last season the Super League table was based on a points per game percentage as some teams played more matches than others. I tried adding this to the league table but it didnt work. Any info will be grateful
Hi Dave, I don't know the exact rules but it sounds like you need to use DAX to calculate a ratio of [Total Points] / [Total Number of Games]. - Mark
Hi..A curiosity question...will there be a follow on tutorial as mentioned at the end of the video?
Sorry for the late reply, but could you tell me how to turn on the PQ Editor data integrity cell checker (for example at 2:59 ) please?
Hi, Imagine I have data like shown in video @0.55.. I want to have a report to display epl matches based on team selected in slicer.. how to achieve it? Any suggestions welcomed
Great video! Do you have a data set for the 2021/23 season?
Hi Nicolae, Great footy data at www.football-data.co.uk/
Good video. However I am not able to get the summation for the "Won", "Lost" and "Drawn" columns. I watched the video several times but still could not figure out what's wrong...
Same here. Could someone explain what he did for Drawn??
Hi inquisitiveYT, Angel, to do the summation, I used Group By Transform in the Query Editor. OOn suggestion - download the PBIX - link to the file in the video description - and open it up and look inside to see how it all works. Mark
@@LondonBusinessAnalyticsGroup Thank you.
Hello.Can you send me epl data in same format for season 2020-2021 for example? I dont know where did you extract it from?
Great video, but I think there is something missing as the table is incorrect. Wolves and Sheffield Utd should be reversed as Wolves have a better GD than Sheffield Utd. The same goes for West Ham, Watford and Bournemouth. So how do you organise a rank based on Pts being the same to then filter by GD and if GD is the same then by GF?
How did you color the result tab?
Is it possible to see in the match statistics if win, draw, lost is based on home of away match ?
I had some fun trying to create this. I have a problem a few teams always look in the league table as seperate teams.
Hi Pieter, the original match record states if teams are home and away so you can do this. Interestingly, my analysis showed that there is home advantage - apart from the 20-21 season when most matches were played without the spectators. The fans do help the team win!
@@LondonBusinessAnalyticsGroup thank you and take care. All the best from Holland
If I have a table with 2 colums (player and team) and in the match stats the first, second etc goal scorer home and away. Is it possible to create a relation between player and first goal scorer and so on for the second goal scorer etc. Or is this not a good approach ?
Please, what is the difference between Duplicate and Reference. In this video, the page was referenced twice, why not duplicate twice? Thanks.
Hello, i want to ask how to find the average goals of GF and GA from the last 5 games of each team? Big push down, thanks for the helpppp
Hi Валентин ,
You will need a DAX measure like this
Average Goals For Over Last 5 Matches =
SUMX(
FILTER(
'Team Stats',
'Team Stats'[ReverseDateIndex]
Can you make a dynamic table with goal week on x axis and point on y axis and each point the top 4 positions
HI Jackson, I'm not quite sue what you mean by goal week. In theory, yes it is possible to make dynamic tables, but if you were to give more details or a sketch, I could be more specific. - Mark
@@LondonBusinessAnalyticsGroup
Yes I have dynamic tables but most of them are bar graphs .I want a graph like a cricket scoreboard where teams score increase with overs .like wise every week the pts of each team in different colour and a symbol .so you get the idea of different top position for each week .
@@jacksonk7362 Yes, you should be able to do a cricket-scorecard - you may need to use a combination of three visuals: cards; tables and matrixes.
I`m doing the same steps biut while in "Team Stats" tab from the video, I get a table where I have the times three times in my table instead of just once. I've repeated the steps 3 times now. Where can I be wrong?
Three time suggests you are getting a row for win, draw and lost. Check your merge.
Hi, I'm Violeta, from Barcelona, Spain.
I am a sports journalism student and passionate about soccer statistics. I would like to have this Excel to enter data from the Spanish soccer league. Can you send it to me please? Thank you.
Hi Violeta. Go to bit.ly/33zWQtp then navigate to the "Football" folder.. This folder contains the Excel data and also a link to the original source data if you want up-to-date data. Mark
@@LondonBusinessAnalyticsGroup And since the file is read-only, how can I unlock it to put my Spanish league data? Thanks again.
how to get sum of points, won, lost columns?
Good skill. Thank, i'm from fb.com/excelball