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")
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
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
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
v2b: (better chart than v2)