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:

https://www.correporelnino.org/

https://twitter.com/CorrePorElNino

#CorrePorElNiño2017

https://www.facebook.com/Carrera-Popular-Hospital-Ni%C3%B1o-Jes%C3%BAs-212488895455880/

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:

http://worktimesheet2014.blogspot.com.es/2015/11/statistics-result-10-km-run-corre-por.html

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:

http://resultadoscarreras.es/index.php?pag=res&idi=ES&car=54

(*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:

https://runedia.mundodeportivo.com/carrera/carrera-popular-corre-por-el-nino-10k-2017/201729597/

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:

https://www.strava.com/activities/1261820177

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:
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-visualization-best-practices/

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:

https://app.powerbi.com/groups/me/reports/ffa20148-280c-4be1-a764-1e3124f26b07/ReportSection

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

Downloads:

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

https://goo.gl/iu1FBV

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

https://www.microsoft.com/en-in/store/p/microsoft-power-bi-desktop/9ntxr16hnw1t?rtc=1&tduid=(947ee81fbddfc4fb21893c22aad2f74b)(263915)(2775081)()()#