Monthly Average vs Current Year Chart
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.
Average Versus Current Period Chart.xlsx (61.7 KB)
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… 🙂
Learn More About Dashboards
Last week I told you about the free webinar on creating dashboards. I hope you had a chance to check it out.
If you want to learn even more about creating dashboards, my friend Mynda from MyOnlineTrainingHub has a fantastic course that will turn you into a dashboarding pro! We live in world with an abundance of data, and the ability to create charts and dashboards is one skill that most employers are looking for these days.
I’ve personally paid for and taken this course, and it really helped me improve my skills. Checkout my new 4 min video and written review of the course for more details. I’ve also talked before about the advantages of taking a course vs watching YouTube videos.
Right now there is a 20% discount available on the course.
And, I’m also going to give you the PivotPal add-in for free! That’s a $37 value. You will also receive my video series on how to create the entire interactive dashboard for Mammoth Mountain using Power Query.
So there are a ton of great bonuses for you!
To get the bonuses:
- Click this link to go to the Dashboard Course registration page.
- Select one of the course bundles.
- At checkout, enter the promotion code: tabs
- I will send you PivotPal and the bonuses a few days after you register.
Key dates for the course:
- 20% Discount ends this Thursday, April 23rd at 8pm PST
- Registration closes Thursday, April 30th at 8pm PST
The course will be closing registration soon, so get signed up before it’s too late. I also have an article where I answer some frequently asked questions about the course.