PowerBI-report UEFA Champions League Titles by Team, history (seasons 1955-56 to 2023-24, winner: Real Madrid)

#89 PowerBI-report UEFA Champions League Titles by Team, history (seasons 1955-56 to 2023-24, winner: Real Madrid)

On 1/6/2024, Real Madrid won the UEFA Champions League (UCL) final from Borussia Dortmund, and with this its 15th UCL-title (!).

I made a Power BI dashboard with all teams that won the UCL (before 1992 called European Cup), from the first season, 1955-56 until now (season 2023-24), here some screenshots.
NB: at the bottom of this blog-post I included the dashboard (embedded, interactive version) + download-URL.

With filter on Country = Netherlands (country #4 in ranking #UCL-victories per country):

 

You can do a drill-through for Real Madrid (-bar) to see the details of all Real Madrid's UCL-victories:

To see how Real Madrid got to these 15 UCL-titles over time, I made a chart with the cumulative # titles.
In this chart you can see e.g. that Real Madrid won the first 5 UCL-finales in a row (with Real Madrid -legend Di Stefano).

NB: The DAX-formula for measure cumulative # titles, I got with help from ChatGPT:

 

 

 

With this decomposition tree chart, you can see a breakdown of the 15 UCL-titles, where I chose the path:  Country (Spain) > Team (Real Madrid) > Coach (Ancelotti, trainer with most UCL-victories, total 5, 3 with Real Madrid and 2 with AC Milan) and Season/Year (2023-24)

To make a Power BI dashboard as this, normally I'd search for the dataset on internet, e.g. Wikipedia, and then transform the dataset for my reporting-needs (e.g. in Power Query). But now there are generative AI tools/chatbots as OpenAI's ChatGPT,  which I used, that can create the dataset you want, or close to it.
For the dataset that ChatGPT generated for me, it missed data for UCL season 2023-24, because ChatGPT has a cutoff date of Jan.2022, so the data for that season I had to add manually.
ChatGPT created some Python-code for the dataset, which can be loaded in Power BI (as it also told me):

 

 

I also asked ChatGPT which visual it would recommend for this data, and it even draw the chart itself (something that was not possible with ChatGPT 3.5, but now with ChatGPT 4o it is):

So these new AI-tools can make the life of a data-analyst and data-journalism work as I did for this post much easier, until one day we are not needed anymore..

If you enjoyed this post and want to make a small donation, you can do this with the Paypal Donate button (at the top right of this post) or via BuyMeAcoffee .

Follow me on LinkedIn

References / Youtube-videos UEFA Champions League (European Cup) history (season 1955-56 to 2023-24)

ALL Champions League goals 2023/24 | Real Madrid

Best Champions League Goals In Football History (2000-2024)

European Cup final 1960 | Real Madrid 7-3 Eintracht Frankfurt

Feyenoord - Celtic 2-1 Europa Cup I Milaan, 6 mei 1970

Ajax wins 1972 European Cup Final

Borussia Dortmund 0-2 Real Madrid | HIGHLIGHTS | Champions League final 2023/24

Downloads

PowerBI report with Champions League history until season 2023/24

Power BI dashboard (embedded)

https://community.fabric.microsoft.com/t5/Data-Stories-Gallery/PowerBI-report-UEFA-Champions-League-Winners-history-seasons/m-p/4000701#M12714

 

 

Results run Cross de Invierno A.D. Ciudad de los Poetas 2023 Madrid

 

#88 Results run Cross de Invierno A.D. Ciudad de los Poetas 2023 Madrid

 

Last Sunday 17/12/2023 was the yearly run 'Cross de Invierno' organized by A.D. Ciudad de los Poetas, in the nice park in my neighbourhood Dehesa de la Villa, Madrid.
For more information about this race, see e.g:

http://adcpoetas.blogspot.com/2023/12/xxxix-cross-de-invierno-clasifiaciones.html

https://youtu.be/WMnQjA7h1oE?si=8O8uilxO4RrOw32O

https://runedia.mundodeportivo.com/carrera/cross-de-invierno-ciudad-de-los-poetas-2023/20233350/ 

or my previous blog-posts about earlier editions of this run, e.g.:

https://worktimesheet2014.blogspot.com/2018/12/data-analysis-for-finishing-times-of.html

For this blog post I made an Excel with the finish-times of the category that I participated in (male, Veterans B), based on this doc/PDF:

https://drive.google.com/file/d/1hcRWlKO9PAoV70GVajWZ79Wn7_YwzCQx/view?usp=drive_link

NB: the table-headers in this doc are in Spanish and abbreviated, but in my Excel, in sheet 'Fields' you can find the English descriptions.

This is my Excel (in desktop version):

 

I've also uploaded this Excel to One Drive and generated the iframe HTML-code to embed this Excel (online version) in this blog (see the bottom of this post).
How-to: https://www.youtube.com/watch?v=uvA-U9FKgPw

In the right bottom histogram-chart you can see that my finish time (00:32:52) is somewhere in the middle, in bin '31:33-33:08' (rank #64 of the in total 113 Veterans B runners).

Steps to make this histogram:

*step 1: calculate finish-time in # seconds, see column O (sheet 'MenAll')

*step  2: for the finish-times (in sec.), generate the bins of the histogram with the 'Data Analysis' function.

How-to: https://www.upwork.com/resources/how-to-make-histogram-in-excel

*step  3: create the bin-labels to use in the histogram (see blue cells in pic above)

How-to:

https://youtu.be/QQGkrYzRbm8?si=t7FeHecvY4Hvyt-M

https://stackoverflow.com/questions/220672/convert-time-fields-to-strings-in-excel

 
*step  4: insert (clustered) column chart (select blue cells in pic above)

On the website of A.D. Poetas you can also find all the photos that were made during the race,
which are a lot (6000). But luckily it has the 'image-search' feature (as e.g. Google Photos), and so I could find easily this nice photo of the run of my kids in this album:

 

or of my race:
https://frutocfotos.barrel.cloud/en-en/album/photo/c731b368-9da4-41d5-b1ed-8bf57adc3734

Thanks A.D. Poetas for the perfect organization again and giving us a nice sporty Sunday morning.

If you enjoyed this post and want to make a small donation, you can do this with the Paypal Donate button (at the top right of this post) or via BuyMeAcoffee .

Downloads

#1: Excel on One Drive (best option, as both are Microsoft products)

#2: Excel on G-drive

 

 Embedded Excel

--

 

--

Messi / Inter Miami / Major League Soccer - Power BI dashboard

#87 Messi / Inter Miami / Major League Soccer - Power BI dashboard 

Now Messi went to the USA to play for Inter Miami in the Major League Soccer (MLS), I wanted to know more about this club and competition and decided to make Power BI dashboard for it, after having updated my Looker/Google Data Studio dashboard 'Messi Goal Tracker' to include his goals for Inter Miami (see this post)

I googled on MLS and API and found this nice API website:

https://sportsdata.io/developers

from which you can get data for free for a big part, although some data is scrambled and only available if you pay)

With the data of some of the API-endpoints of this site I was able to create this dashboard:

In this pic you can see 2 pages of the dashboard:

-1: the competitions Inter Miami plays in, so beside MLS also the League Cup (international competition with besides clubs from the USA also clubs from Mexico and Canada)

-2: bubble-chart with total number of US-clubs in MLS per US-state.

And here some pics of other pages in the dashboard:

This ArcGIS filled map shows the number of players from Inter Miami that come from that country  (lighter color in map means higher value), which shows e.g. that there is one Dutch player, Nick Marsman (played before for Feyenoord), although this info looks to be outdated (Marsman already left Miami). And with a right-click on a country, you activate the drill-through feature that leads you to another page of the report, with player-info, that is pre-filtered with the country that you selected.

And here the full details of the MLS-teams and of the Inter Miami-players:

Some more details about the datasource of this dashboard, so the Sportsdata.io soccer API-endpoints:

https://sportsdata.io/developers/api-documentation/soccer

 https://sportsdata.io/developers/api-documentation/soccer#/endpoint/competition-fixtures-league-details

The API-endpoints I needed, I stored it API-tool Postman.
NB: for a Postman-tutorial, see e.g. this video

And this API-call information I then copied from Postman to Power BI, in datasources of type 'Web', including the parameters of the API-call, e.g. team Inter Miami = 2363:

NB:

For some more details on how to work with parameters in Power BI / Power Query, see e.g.:

https://learn.microsoft.com/en-us/power-query/power-query-query-parameters

https://msdynamicsworld.com/story/use-parameters-and-custom-functions-call-apis-power-bi

 

Messi shows he is still in a good shape, see e.g. his first goal for Inter Miami, and David Beckham's reaction:

https://www.cbssports.com/soccer/news/lionel-messi-scores-dramatic-94th-minute-winner-in-inter-miami-debut-against-cruz-azul-in-leagues-cup/live/

https://www.tiktok.com/@_forca_barca/video/7258589957751655682

Here's the API-output (json-format) for that goal:

MLS soccer will be happy that Messi has chosen for the US to finish his career as earlier other soccer legends did, as Cruyff, Pele, Beckenbaur and more recently, Beckham, see:

https://en.prothomalo.com/sports/football/38nc6f2jmd

 

Embedded Power BI dashboard

 NB: I also published it on the Power BI Data Stories Gallery:

Messi / Inter Miami / Major League Soccer - Power ... - Microsoft Fabric Community

 

 

Download  

Power BI Dashboard