25 Feb 2018

Speed skating at Winter Olympics: Power BI Dashboard for all medals

#62 Speed skating at Winter Olympics: Power BI Dashboard for all medals

For the 2018 Winter Olympic Games in PyeongChang (South Korea), in Korean:
평창 동계 올림픽 

I made a Power BI dashboard for all medals won in the long track speed skating competitions, see fig.1 for the result

fig.1: Power BI Dashboard with medals/rankings for all speed skating competitions

NB: translations Dutch - English
- Massastart =  Mass start
- Ploegenachtervolging = Team pursuit

The datasource I used is:


NB: This is the Dutch version of this English page:

but I choose to use the Dutch page as the tables on this page (with medals-table and competition-results) where in a better format to import them in Power BI.

This datasource had a table with per competition a row with the athletes with rank 1, 2 and 3 (see the tables at the bottom of the dashboard), which with Power Query's M can be easily be unpivoted, so in 1 row per rank (needed to do the aggregations, leading to the tables and charts in the middle of the report).
For more info about (un)pivoting tables with rankings, see e.g.:


As you can see in the report, the Netherlands did very well again this Winter Games, and was #1 in medals-table for long track speed skating with16 medals in total. Besides that we won another 4  medals in short track speed skating, so we won 20 in total, see for all medal-winners:


Only in the previous Winter Games in Sochi the Netherlands did better.
For my post about these medals ( so in Sochi), see:


And for a nice chart with Dutch medal-scores at all the Winter Games, see:


As I told in my previous post about the 2018 Winter Games, see:


skating is a very popular sport in the Netherlands and we did send the max. allowed number of skaters to the Winter Games (20), more then any other country. So you could say that it is logically that we win the most medals in skating.  I read here:


and interesting article which ranked the Netherlands #2 (after Norway) using a special 'power rating', which not only looks at the medals won, but also to the ranking of the other (not medal-winners) athletes, the # of participating athletes etc.).

Well done #TeamNL !!


Mirror #1 (Google Drive):


18 Feb 2018

Power BI chart with map for 2018 Winter Olympics

#61 Power BI chart with map for 2018 Winter Olympics

For the 2018 Winter Olympic Games in PyeongChang (South Korea), in Korean:
평창 동계 올림픽 

Google did some research of what the favourite winter sport of every country is, and published this on Google Trends, see: 


Not surprisingly, for the Netherlands the favourite sport is (ice) skating (speed skating and shorttrack). I was interested to see how popular skating is world-wide, so I looked at above Google Trends site for 'ice skating', filtering for the period of the Olympics 2018 (9 Feb. until today, 17 Feb.), here:


I downloaded the CSV file from Google Trends and with this I made the Power BI chart in fig.1.

fig.1: Power BI 'filled map' chart with Google Trends Index for ice skating

The only red country on the map, left to the 'E' of Europe, is the Netherlands, with the highest Google Trends index (of 100) (and in green are the lowest scoring countries, and in pink the countries with a medium score).

To make this chart, this site was very helpful for me:


For some information about Korean, see my blog-post: 

-For some of my blog-posts with skate-statistics of the 2014 Winter Olympics, see:

13 Nov 2017

Statistics result 10-km run Corre por el niño 2017

#60 Statistics result 10-km run Corre por el niño 2017

Note 13/11/2017: updated post and report (see 'Downloads, v2)

On Sun. 5 Nov. I participated in the 7th edition of the 10K run "Corre por el Niño 2017", with this year about 10.000 participants (10 km, 4.5 km and 2 km for kids (whole family)).  This run is organized by hospital Niño Jesús (in Madrid), to raise funds for investigation of severe diseases with childeren, like e.g. cancer. For more info about this road-race, see:



And besides the 10K I also did the 2K 'run' (which started later) with the family. The company Bebeaway sponsored us with a running buggie, for which I'd like to thank them. If you are interested in hiring a running buggie from them, see:  http://www.bebeaway.com/

And now about the 10K race. As in previous editions in which I participated, I made a data analysis of the finish times. In my last post about this race in 2015, see:

I used Excel for the data analysis, but this year I decided to use Microsoft  Power BI, a 'Self-service' Business Intelligence (BI) solution, which could be a good alternative for Excel (for BI-stuff). For the end-result, see fig.1. In this figure you can see that in this dashboard you can filter by (runner-)name and starting-number of the runner, which then shows in the top-table the netto finish time (*1) of the runner, his/her category (gender + age-group) and the ranking (general and per gender and per category), the same as the site of the run with the results, see:

(*1) Note: the official time for this race is the bruto-time, but I chose to use the netto time in my data analysis as I think this is what counts for most runners (who press 'start' on their stopwatch when they cross the start/finish line, which can be some time after the 'start' gun shot, with a field of 1820 runners).

Besides that, I also included the percentile rank in the dashboard, for which I used the system of World Masters Association (WMA), which takes for the 100% percentile the best finish time and so a percentile rank of 74% in this case means: the runner was 26 % removed from the best time (his performance was 74% of the best performance). For more details, see: http://livehealthy.chron.com/fast-average-runner-run-5k-10293.html
BTW: on this site, which also has created statistics of the race:
the percentile rank was defined just the other way around: the fastest runner is in percentile 0% (and the last runner in 100%).

Fig.1: Power BI dashboard for 10K race finish times

The visuals in the part below the table with the result of the selected runner are competition-statistics (so not for an individual runner as the top-part, but for all runners). In fig.1 I selected in the chart '#Runners/Category' the category 'VeC M' (veteran C Male), so the same category of the selected runner, and the report shows there were in total 214 runners in this category, where the time of the fastest runner was 00:38:23, so the the performance of the selected runner (with time 00:49:22)  was 67% of that of this fastest runner.

When I made the report for the 2015-race, the 'category' was not registered, but luckily now it is, which makes the data analysis more interesting and is also good for 'bench-marking' (how well did I do comparing to my 'peer-runners' (same gender and age-group)).

For this race I also use the runner-app 'Strava', for the result, see:


NB: I pressed 'start' some time before the race really started, as I wanted to be sure the app was working OK.

That's if for now, maybe later I'll give some more details of how I made this dashboard with Power BI.

V2 (update 13/11/2017):
After having read:

I have improved the design of the dashboard, e.g.: the filters are now left to the table they are applied upon.

I also uploaded my dashboard to the Power BI cloud service:

and in fig.2 you can see how a 'published report' in the cloud looks like.
In this case I first looked at the bottom-left table who was the winner of the run (general ranking), which was Martin Fiz, in 00:33:10 (!), and then I filtered in the upper table on his start-number (click in 'slicer' on '...' > Search and then fill '0123' and click on the loupe) and I learned that he is from category Veteran-D (he is 54 years), and finally I filtered on this category in the bottom-right chart (click on bar 'VeD-M', which then filters all 3 visuals at bottom part of report) and the bottom-left table shows then that rank 2 in this category finished more then 7 min. later. I did not know Fiz, but it appears he is a Spanish former professional marathon runner, and is still in a very good shape it seems. So that could be an inspiration for middle-aged men like me :)

fig.2: Power BI report in the cloud

Another nice feature of Power BI is 'Q&A', which allows you to query the dataset of the report in 'natural language', see fig.3.

fig.3: Q&A


#Mirror 1: Google Drive (zip files with Excel and PDF files):


- to open the PowerBI (pbix) file, you need to have Power BI desktop, for the (free) download, see:

19 Dec 2016

Carrera de la Empresas 2016 Madrid Statistics made with R

#59: 10K Carrera de las Empresas 2016 Madrid: Statistics made with R + included in Power BI

Note 20/12/2016: I updated this post with a Power BI solution (with R-Stats and Q&A), see part 2 of this post.

Part 1: Statistics with R 

Today the 'business run' ("Carrera de las Empresas") was held here in Madrid. I participated with Raet, a Dutch ISV of a SAAS-solution for Human Resources, which since almost 2 years now has a software development centre in Madrid.

For an earlier post of this run (with more details), see:

This time, there were over 14.000 runners, from 800 companies, for more details, see:

The results of the 10K run (in which I participated) I got from here:

and for the statistics, see this page (a social/runners network): http://www.runedia.com/cursa/201628906/carrera-de-las-empresas-10k-actualidad-economica/2016/

The results (10K-Men, 5563 runners)  are in PDF format, which I converted to Excel, with help of this nice (free) online tool:

After some 'cleansing' (see added yellow-columns in Excel file) I had the data ready for analysis, for which I used R, the language for statistical computing.
NB: For an earlier post in which I used R, see:


The R-script I made looked basically like this:

sv10km2 <- read.csv("10Km2.csv")
plot(density(Tfinish,na.rm=TRUE),lwd=3,xlab="Finish Time (mins)", main="Madrid Business Run 2016 10k Results")
boxplot(Tfinish, data=csv10km2)
newdata <-  sqldf('select * from csv10km2 where Team like %Raet%')

Input for the script has to be in the MyDocument-folder, in my case: D:\Persoonlijk\maartenree\Documents\10Km2.csv

See figures below for the result of my analysis.

And for those who don't have R, but want to do the same as I did for my company (in fig.3), so filter the results, I also added the Excel where the finish-results are in table-format (so easy to filter).

Fig.1: Plot with finish-times and Five-number summary

Fig.2: Boxplot  with finish-times

Fig.3: Filter dataset with finish-times for (teams of) company Raet

Part 2: Power BI + R Stats/Charts

Power BI has an option to include R Stats/Charts, and so I wanted to add the above R-chart 'Plot finish-times' (fig.1) to Power BI, and the filtering-option in Power BI would make it easy to 'slice-and-dice' this chart, in this case: filtering for a team, and showing the runners in that team. To be able to do this, I had to convert the Excel-column with the finish-times in 'duration-format' ([h]:mm:ss) to seconds, see column Tfinish2 in attached file 10Km3.xlsx and fig.4.

Fig.4: Excel with new column Tfinish2 (time in seconds)

And for the final result in Power BI (Desktop), see fig.5.
Note that slicer for team 'dimension' has a pre-filter (in this case with value 'Raet') to limit the number of items in the slicer (very helpful when there are over 1000 teams). And to filter for >1 team (like I did, with 3 Raet-teams 'checked'): press Control + click in checks for filter values. And note that the chart is reacting on the filter (so Power BI filter and R-chart are connected).

Fig.5 Power BI Desktop: report finish-times (in second) filtered for teams of Raet

I also uploaded the Power BI Desktop report to Power BI online, see:


To be able to use R-Stats/Charts in Power BI online, you need to have a Power BI Pro account. About how to get a free trial-account, see:


With Power BI Online it is also possible to do Q&A in natural language (English). See fig.6-7. For this example to work, I had to rename the Excel-columns Runner -> RunnerName and Team -> TeamName, see:


Fig.6 Power BI and Q&A 1/2

Fig.7 Power BI and Q&A 2/2





v1: https://goo.gl/gdjPRk



v2b: (better chart than v2)

9 Dec 2016

PowerBI Dashboard for Messi´s worldrecord of 91 goals in one year

#58 PowerBI Dashboard for Messi´s worldrecord of 91 goals in one year

- This post is not about an Excel I made (but about a Microsoft Power BI dashboard I made, which is (almost) a copy of an Excel workbook I blogged about earlier), so I tagged it with the 'More' category.

Once I made an Excel with a PowerView dashboard for Messi's worldrecord of #goals in one year (91), see:


In this post, I made a new version of this dashboard, but now in Microsoft PowerBI, and also with some improvements, e.g. the treemap-view for all matches in which Messi scored, aggregated by the team and competition where he made his goals. NB: For more info about the Treemap-chart, see my previous post:


See fig.1 for the end-result, in PowerBI Desktop.

Fig.1 Messi-Dashboard in PowerBI Desktop

The nice thing of PowerBI-dashboards is that they are suited for any device, so apart from PC also smartphone or tablet, see fig.2, in PowerBI-online (cloud-service), so this is a site for sharing your dashboards, for mine, see:


This URL Power BI gave me after choosing menu-option: "File > Publish to web", which should make the dashboard 'public', see:

Or you can use this URL: https://app.powerbi.com/groups/me/dashboards/729276ac-365c-4473-9d42-1969ddd16dac

There is also a Power BI Community page which has a 'Data Stories Gallery', and I posted my 'Messi-dashboard' here:

And here I found another nice Messi-dashboard:

So this is similar to this page of the Tableau-community:

Fig.2: Messi-Dashboard in PowerBI (online) on PC and smartphone

2 Nice build-in features of Power BI are:
- Quick Insight in the dataset (source of dashboard), which generates several 'data-profile' charts, see fig.2b
- Natural language Q&A, user types his question (in English), using some 'keywords' like 'by' to specify the 'slice-and-dice' dimensions, and Power BI answers (with charts) , see fig.2c.

For more details about these features (and Power BI in general), see:

Fig.2b: Quick Insight

Fig.2c: Natural language Q&A

As said in my previous blog-post, my Messi-dashboard was inspired by one made with BI/Dashboard-tool Tableau  (for which PowerBI was Microsofts answer/alternative), see fig.3 for the this version.

Fig.3: original Messi-dashboard in (free trial version of ) Tableau

To see another really nice example of a PowerBI-report with the results of the Spanish soccer League made by a friend, see fig.4 and his site: http://Ligasantanderbi.azurewebsites.net

Fig.4: PowerBI (with Azure) example with results Spanish soccer League

So with Power BI you can make really nice dashboards, and with the frequent updates/releases of Power BI, much more frequent than that of MS Office (Excel), one could wonder if for 'self-service BI' Power BI is the way to go (and not Excel with its 'Power-plugins' like Power View, Power Map etc.) Some say it is, see e.g.:


while others say you can/should use the 'pack' 'Power BI + Exel', see:

And what do you think? Comments are welcome (but please not anonymously, because I get a lot of 'fake' comments (publicity) from anonymous users, so I don't read anonymous comments.

Note 12/12/2016:
Although I haven't got any comments on this post (yet), apparently the 'editor' of The #PowerBI Daily (on www.paper.li), liked this post, because he published it here today, see fig.5.
NB: Paper.li is a site to create your own (virtual) newspaper of tweets of persons you follow, for more info, see:

Fig.5: Blog-post referenced on  The #PowerBI Daily (on www.paper.li) 


-1: Good explanation of what is "Business Intelligence (BI): Multidimensional Analysis"

-2: A 'BI-solution' actually consists of 2 components: a Data Warehouse (DW), the (invisible) back-room ('the database') and BI, the (visual) front-room (the dashboard), where DW is normally about 80% of the solution, and BI 20 %, so it is the top of the ice-berg, as described here:

Pbix-file, so PowerBI-report:


17 Jul 2016

Treemap and Sunburst Excel2016 charts for Tour De France

#57 Treemap and Sunburst Excel2016 charts for Tour De France

In 2015 Tour de France contracted the company Dimension Data to make the event as high tech as possible. As DD said themselves: 

It has completed its big data analytics and digital delivery platform from which the company will deliver real-time information on individual riders for the first time in the history of professional cycling. This major innovation was made possible by Amaury Sport Organisation (ASO) in partnership with the 22 teams participating in the 2015 Tour de France. Highly accurate data will be collected through the use of live trackers under the saddle of each rider. Dimension Data will then process and analyse the data, and make it available to cycling fans, commentators, broadcasters and the media.

When the Tour de France begins on Saturday, the viewing public around the world will be able to follow all 198 riders in 22 teams real-time, and be able to track the speed at which each cyclist is riding, exactly where he’s positioned in the race in relation to other cyclists, and the distance between each rider - all via a beta live tracking website.

And I think they did an excellent job, and also in this year's edition.
On one of their Twitter-posts:
 I once saw a graphic (data: total % of time a cyclist was leading in the breakaway group) which I didn't know, and which was something which could not be made with Excel (2013), but a bit later, which the launch of Excel-2016, I learnt that this graph was a Treemap, so now supported by Excel, and I tried to reproduce, see e.g. fig.1

fig.1 Treemap chart wit data from teams and cyclists

Besides the Treemap chart, Excel 2016 had another new graph, the Sunburst chart, see fig.2 for an example

fig.2 Sunburst chart wit data from countries and cyclists

Both the Treemap and the Sunburst-chart are for displaying hierarchical data, so e.g. in Tour de France, cyclists are part of a team, so you can compare the teams performances (total times) and breaking it down to the indivual cyclists performances, as done in fig.1. Another possible grouping is cyclists per country as in fig.2, although this is more artificial, so not something which is considered by the Tour de France, while grouping by teams is of course (there is a prize for the best team). 

And if fig.3 you can see the equivalent of fig.2, but now in Treemap format. Maybe this format is handier if you have less space, like e.g. Dimension Data had, where this chart was part of a dashboard with other charts.

 fig.3 Treemap chart wit data from countries and cyclists

And in fig.4 you can see the original Dimension Data Treemap, which in fact did not have any hierarchical data, just data of cyclists. In this chart it is of course the easiest to compare data of 2 riders, e.g. the 2 best riders in the breakaway-group were Roland and Rodriguez (both in left of chart, with same square-size).

fig.4: Treemap charta with data from cycists.

If you want to read more about Big Data brought by Dimension Data to Tour de France, see e.g.:

And for a video explaining more about Treemap charts in Excel, see: 

For me, the new high-tech dimension (also by using onboard cameras) makes the Tour de France much more interesting to follow, and of course the fact that the Dutch are doing very well in TDF2016 (Tom Dumoulin 2 stage victories and Bouke
Mollema #2 in general ranking).




5 Jun 2016

Month-calendar for June 2016 in Excel

#56 Month-calendar for June 2016 in Excel

In this post the new month-calendar for June, see fig.1. The Excel also has the year-calendar for 2016 and a 2016-year-planner. Event of this month is UEFA Euro 2016 France, hold on 10/6 - 10/7. Unfortunately the Netherlands did not qualify, so this time I was not so motivated to make a new version of Match for this Eurocup, or a new Excel for an office-pool, like I did for last FIFA Worldcup. But of course you can do it by yourself, by adapting these 2 files (Access and Excel):



For a very nice Excel with a EURO 2016 tracker, which automatically calculates the points, who go to next round etc (by using formulas), see:


fig.1: Month calendar 201606