Introduction

For the fourth day of Dashboard Week, the challenge was to use the website https://moneypuck.com/data.htm to collect data on American ice hockey statistics across various metrics. As someone who knows little about ice hockey, this presented an interesting opportunity to analyze data that I had limited knowledge of. With more research, I discovered that ice hockey is a complex game, and the dataset contained hundreds of field names for various game metrics. For this reason, I decided to base my analysis on one player. After consulting Google for information on players with successful careers in ice hockey, I came across an American player called Patrick Kane. I chose to produce a report on Power BI that included an interactive timeline of his career, featuring key events in his life juxtaposed against a line chart of various performance metrics. In this blog, I will explain how I utilized the native tools in Power BI to create this report.

Data Cleaning and Preparation

The raw data from the website was in good condition, so cleaning in Alteryx didn’t take long. For this visualization, I needed three tables. The first table included all performance metrics, including a date column indicating when the season occurred (data was aggregated to the season level). The second table encompassed event data and event descriptions, also with a date column. Finally, I created the third table using the following DAX code for a date table. Once the tables were created, I loaded Power BI and established the relations, linking the tables to the date table

 

Steps:

  1. Create a Line Chart visualization and add the date from the date table to the x-axis
  2. Create a measure for the Secondary Y-axis position (this will establish a position to add event descriptions
  3. Place that measure on the Secondary Y-axis. This will create a placeholder for the event description
  4. Edit markers in the format by turning them on and decrease the line stroke width to 0
  5. Turn on data labels, navigate to label values, and enable custom values. Add the Event Description to the field in the data panel.
  6. Set the label density to 100 and adjust the positioning of the labels.
  7. Navigate to the analytics panel for the visualization, access the error bars section, and include the y-positioning measure created earlier as the upper bound
  8. Create a new measure for the lower bound and set it to the integer value of -2.
  9. Add a zoom slider action to both the secondary Y-axis and X-axis. Turn off values and titles for the Secondary Y-axis.
  10. Add the measure you want to track to the Y-axis. 
  11. Access the formatting options and modify the line properties. Ensure you select the measure in the series dropdown, then add the line stroke width and change the line type to smooth.
  12. Edit the colors according to your preferences

Summary

By aligning performance measures with specific events, you can analyze whether there’s a correlation between certain occurrences and changes in performance. This correlation analysis can help identify factors that contribute to fluctuations in performance. Events can provide context to fluctuations or trends in performance metrics. Understanding the timing and nature of events allows for a more nuanced interpretation of the data, helping stakeholders grasp why certain changes occurred. To look at my Power Bi report click the link: https://www.novypro.com/profile_projects/felixralphs  and head over to my Novy Pro page.

Felix Ralphs
Author: Felix Ralphs