One of my goals this year was to build a report that I can present to kids and parents showing the skills progression of their kids. I have attempted a few times to put something together in Excel, but it wasn’t easy for parents to visualize, and I wanted something better to let them focus on a specific kid, without making the data hard to consume. Power BI makes this easy.
This post looks at my data capture process, and how I evolved it a bit to make this easy to handle in Power BI.
Data Capture
I’ve tried a few ways to gather data during matches, but paper turns out to be the easiest way to ensure this happens quickly and fairly accurately. While there are a number of apps, I find them problematic as one wrong press means the data capture stops, and I can then miss the next item.
I used to calculate all totals by hand and then put them in a spreadsheet for parents after each competition, which worked well, but this format isn’t easy for Power BI to deal with.
Easy for humans, but bad for reporting.
As a result, I stopped to think what would be good and easy for Power BI. A table is best, and while I don’t want to bother with a database, I can modify my Excel formula easily enough to handle this.
Since I will report on different areas, I decided to keep a master sheet for each report area. This means I have a “serve” worksheet, as well as others for Serve Receive, Attack, Digs, Blocks, and Assists. I can also add in new sheets as needed.
Making a tabular format means that I added a few columns to this list. These columns are the slicers that atheletes and parents might want to use when they are reporting. In my case, these are:
- date of event
- event name
- opponent
- player
With these columns, I can take my paper sheets, type in the raw data, and let Excel do a few calculations. This also means my main report is just a few sums from these raw sheets to get the totals above. From last season, I had data like this:
I also decided to enter data in the same order each time so that once I have a sum to copy data from this sheet for one player, I can copy/paste those formulas for the rest. This keeps the burden low for post game work.
This also means that when I “Get Data” in Power BI, I just load data from each worksheet into a separate table in Power BI. This allows separate reports that are simpler to produce, as much of this data doesn’t make sense when combined together. This also means that I don’t have one huge table where I’m trying to manage data and potentially scrolling around a lot from left to right. This also means I can load this into SQL Server easily if I want to.
This also means I need to set up incremental refresh in Power BI.