Bottom line: Learn how to create this interactive chart that displays monthly averages versus the current year.
Skill level: Intermediate
Mammoth Mountain is my wife and I's favorite place to go snowboarding in the winter here in California. It's about a six hour drive from where we live in Southern California, and well worth the long haul when the snow is good.
Unfortunately, the snowfall was almost nonexistent this year, and a bit of a bummer. So I had plenty of time to analyze why it was so bad… 🙂
Getting the Data
Mammoth Mountain makes their historical snowfall data available on their website. They have a table that shows monthly snowfall (in inches) for the last 40 years. Here is the link to the webpage.
You can copy this data and paste it in Excel. Or, you can use Power Query to connect directly to the webpage, bring in the data, and then unpivot it into a normalized table that can be used for a pivot table.
Once this is setup in Power Query, you can simply click the refresh button every month to pull in the new data directly from the website, and also update your pivot tables and charts. It's truly awesome!
The Monthly Average vs Current Period Chart
I'm not exactly sure what to call this chart, but my basic question was… “What is the historical average snowfall per month compared to the monthly snowfall this year?”
This is a basic Clustered Column Chart that displays the average snowfall by month for all the seasons (years), compared to the monthly snowfall for the selected year.
Looking at the winter months of Jan, Feb, and Mar you can start to see why the snow wasn't that great in 2015. Every month this winter was lower than average, and January felt more like a summer month. 🙂
Download the File
Download the file to follow along.
Setup the Source Data
The source data for this chart is really simple. We have one column that contains the average snowfall over the 40 years, and a second column that contains the snowfall for the current/selected year.
I used an AVERAGEIFS formula to calculate average snowfall in column C.
The AVERAGEIFS formula contains three criteria:
- The Month in the data table in equal to the month in column B.
- The Season/Year in the data table is greater than or equal to the Start Season.
- The Season/Year in the data table is less than or equal to the End Season.
This allows us to change the Start and End Season for the average calculation. So if we wanted to see the average snowfall for the last 20 years, we could change cell C3 to 1995. Or use the formula =C4-20.
You'll also notice that the formula contains Table and Column Names (tblData[Month]) instead of cell addresses (Data!$B$2:$B$507). This is because the data output from Power Query is in an Excel Table that I named tblData.
Excel Tables are extremely useful for calculations like this because you don't have to worry about updating the cell references when more data is added to the table.
By the way, this AVERAGEIFS formula could have also been easily calculated with a pivot table.
Pivot Table & Slicer for Selected Year
For the current/selected year I used a pivot table to calculate the average. This allows us to connect a slicer to the pivot table, and then display the data in the chart.
The slicer makes the chart interactive, and the user can quickly select a year to compare it to the average of all the years.
The nice part about using a pivot table and slicer is that it makes the chart interactive without having to use VBA macros. It's a great addition to any dashboard.
Dynamic Chart Series Name
I also used formulas to create the Series Names for the chart. These are the names that are displayed in the legend of the chart.
Since the chart is interactive, it's best if the names in the legend are also updated when the user changes the chart.
Putting the Chart & Dashboard Together
Once the data is setup we just have to create the chart and put it all together.
Select the data range and choose Clustered Column Chart from the Insert Chart menu. Now we just need to clean up the default formatting, and add the chart to the dashboard.
The dashboard also contains a Column Chart that displays the seasonal snowfall trend from 1970-2015. That chart actually contains two data series to display the selected year in orange. I explain more about the using the secondary axis in this article about waterfall charts.
The following screencast shows the dashboard in action.
Analyzing the Data
Well, the 2014-2015 season was one of the worst years in history for snowfall in California. Our winter months (Jan, Feb, Mar) were extremely low compared to average. But, we are optimistic that it will climb back like it did in 2007-2011 (record high year).
I know our friends on the east coast got hammered with storms this winter, so I'll try not to complain too much.
Fortunately, we made the best of it with lots of warm weather and great surf this winter. It's hard to complain about anything in Southern California, except for the cost of living… 🙂