Exploratory Analysis in Power BI

With all the data together, the next step in our Fantasy Basketball Optimization series is to do some basic exploratory analysis on the data. We’ll use a tool called Power BI for this, which a Microsoft tool for creating interactive data visualizations from a user-friendly “drag and drop” interface.

While most fantasy apps provide a variety of different stats and metrics, Power BI allows us to create custom visuals that are tailored to specific goals/questions. For instance, we wanted to see how much higher the actual locked totals were compared to single-game totals. This isn’t shown in the Sleeper app, but we created it in Power BI.

This article showcases how we imported the data from our relational database of CSV files into Power BI and created tailored visuals to help with our fantasy basketball team.

Power BI Insights

Here’s a quick showcase of our full Power BI report page:

We’ll start by showcasing some of the insights we got from the visuals.

Measuring the Lock-in Bias

First, we have our matrix that breaks out the lock-in bias calculation across the different players and positions:

This shows that the overall lock-in bias was 37.5%, meaning that the final locked player average was 37.5% higher than the single game average (locked average was 53.8 points while the single game average was only 39.1 points).

It also shows how this lock-in bias differs across different players and positions. For instance, we see that point guards typically have a higher lock-in bias (42.2%) than point forwards (20.4%). Thus, when both have similar single game averages, point guards are typically more valuable than point forwards.

Analyzing Player Positions

We also have a few visuals that breakout stats by position/team. The first showcases single game averages:

We see that point guards have the lowest single game average, but we know from earlier that points guards also have the highest lock-in bias. Thus, point guards are an undervalued position that we can capitalize on (as seen in the player counts by position visual):

Performance over Time

Our last visual shows a time series of total points per week, which shows us how our overall performance is trending over time.

The visual shows that we were relatively inconsistent at the start of the season, but started getting more consistently high totals after about week 10.

Report Setup

We’ll also do a quick overview of what it took to create this Power BI report. We split the work up into these 3 main pieces:

• Query Editor
• Data Model
• Visuals

Query Editor

We first needed to load the data in through the query editor. While some tables were simply loaded straight from the CSV file, others required some transformations, which we showcased with this flow chart:

Data Model

The data model is mainly centered around the Players table (which identifies each player with the PLAYER_ID attribute) with a few other tables connected:

• Stats
  • This breaks out player stats by game
  • We setup a 1 to many on PLAYER_ID so that we can aggregate single game stats in the Players table
• Locked Points
  • This holds the final locked totals for each player/week
  • We also setup a 1 to many on PLAYER_ID here to aggregate locked totals per player
• Player Positions
  • This identifies which positions a player is eligible for
  • Since fantasy basketball players can hold multiple different positions, there is a 1 to many relationship to the Players table (also on PLAYER_ID)

We also added a few measures to help with the visuals. First, we calculated both single game average and locked averages:

Avg Points = AVERAGE(Stats[FANTASY_POINTS]) 

Locked Average = AVERAGE('Locked Points'[LOCKED_POINTS]) 

We also counted the distinct number of players using the PLAYER_ID attribute:

Players = DISTINCTCOUNT(Players[PLAYER_ID])

Finally, we calculated the lock-in bias as a percentage of the single game average:

Lock-in Bias = IF(
    ISBLANK('Locked Points'[Locked Average]), 
    BLANK(), 
    DIVIDE([Locked Average], [Avg Points]) - 1
) 

Visuals

The visuals are pretty straightforward, most of which just showcase stats in matrix visuals. The first showcases lock-in bias by position/player:

There are 2 other matrix visuals that help us analyze different positions and how our team compares to the rest of the league:

1. One with average points by position (broken out by team with the ability to drill down
2. Another with player counts by positions

We also added a line chart with total locked points per week and paired it with a matrix to drill down to individual player performances by week:

Conclusion

With Power BI, we can visualize our data to target specific goals/questions and ultimately derive better insights. In the upcoming posts, we’ll take this analysis a step further with more advanced statistical modeling and Monte Carlo Simulation to solve our team’s challenges.

Feel free to download the full .pbix file from our GitHub repo to try it out yourself (note you’ll need to retrieve the data yourself by following the previous posts in our Fantasy Basketball Optimization series).

If you found this guide helpful, please drop a comment below with questions and/or feedback.

Thanks for joining us today, and feel free to checkout our next post in the series: Addressing the Lock-in Dilemma.