The PowerBI Volleyball Report – Organizing Data To Start

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.

2021-07-28 14_12_31-Stats2022_16Select.xlsx - Excel

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:

2021-07-28 14_19_37-Stats2021_15Select.xlsx - Excel

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.