Approach

The brief of this challenge was to "build a one-page dashboard to help skiers find their ideal destination."

This challenge was pointing towards a dynamic exploratory report rather than the traditional static one pager. As such, my approach was to create an "app like" solution whereby a user could input a set of choices and be given a suitable recommendation(s).

Considering there are 499 resorts in the dataset, I thought about how I could effectively create a ranking system for the resorts that would take into account both the users selected criteria, and also other more general factors such as the likely snow coverage for a selected month.

As the end user is the general public, I wanted something simple to use and navigate, viewable on a single computer screen, and provided the necessary information for a user to find their ideal resort, and then learn about some of the key information to allow them to make a decision.

Issues and Hurdles

There were several issues and hurdles to overcome on this challenge to enable me to produce my final report. Below are some of the more salient from easiest to most challenging:

Grouping

Rather than creating selections based on simple numbers like length of slope or price, I opted to create ranges. This would help group resorts together and simplify the selection process for the end user.

For example I grouped price into low cost being 0-30 Euros, mid cost being 31-60 Euros, and high cost being more than 61 Euros. This was based on an analysis of distribution for each category. I created groupings for the resort size, based on the total length of slopes, and also on the overall skill level, based on any skew in the ratio of beginner, intermediate and advanced level slope lengths.

Dates/Month:

The main data table contained information on a ski season (beginning and end). To enable an end user to select based on a month rather than a season, and to further align with snow likelihood, This required the creation of a date matrix, followed by a left join with the resorts and seasons so that I could list months against a resort rather than seasons.

Snow Coordinates:

Connected to the months above, I wanted to align the snow coverage with each month, but this required alignment with the resort coordinates at the snow data coordinates, which have arbitrary rounded figures. I actually solved this one in excel using a combination of INDEX:MATCH and ABS(MIN()) to find the closest coordinate with snow data. This was again left joined with the above to provide snow against each month for each resort.

Ranking Resorts:

I needed a method to allow me to differentiate between the appeal of each resort. I settled on normalising and factoring a number of the numeric parameters with scores between 0-1 using the following formula and a series of list creations in power query.

This included:

• Total slope length
• Elevation Difference
• No. of Ski Lifts
• Lift Capacity
• No. of Snow Cannons
• Longest Run
• Price

I also added a normalised factor for snow coverage for each month. I then refactored these scores with 30% allocated to snow coverage (deemed most important), and the remaining 70% allocated to 7 factors listed above.

This meant that resorts that may score highly during peak snow periods; however, their score will reduce towards the end of their season when snow coverage is low.

This ranking exercise was the crux in determining the "Top 3" resorts being chosen by any criteria selected by a user.

Creating Visuals Showing the Nth Element with DAX

The trickiest part was creating DAX measures that allowed me to show the 2nd or 3rd ranked items as standalone visuals, such as cards. It is quite straightforward to show a Top 3 list in a table, but more difficult to show the "3rd ranked" category, especially when making it dynamic and responsive to multiple slicers, as filter context usually comes into play.

Luckily I stumbled upon this excellent article by the DAX guru Marco Russo.

Nth value creation in DAX

I wont go through this in detail - you can read the article for yourself - but I slightly amended the process, by using the DAX to effectively dynamically hard code the Top 3 resorts, and then use that value to create dynamic filters on the other calculations I made for the rest of the report.

Bringing it together

To create that app like report, I created a simple page providing key stats and information for the Top 3 resorts, with the focus on number 1. There are no complex visuals or analysis on display for the end user to decipher, all the smarts is "hidden under the hood".

I used icons with accompanying categories on the rank 1 resort, then omitted the words for rank 2 and 3, as users may be familiar with the meaning as they are quite intuitive.

I removed the slicers from the main page by creating an overlay button with built in background pictures (shout out to my mate Peter Doan in Canada for some cool ski pictures he takes on his travels there!). I then simply used the bookmark feature to show and hide that panel and provide a refresh.

The panel allows the user to select the most important aspects first, being their timing and location, and then further customise with a few high level parameters such as resort size, ski level and price ranges. A handy information button was included to add a little more context into what each selection consisted of.