SQL Business Intelligence Professional

In the spirit of the Scientific Method, I was recently creating a PowerPivot solution using the calculations for relative airline performance published on the “Things in the Sky” blog.  In short, the author was using the Bureau of Transportations Statistics data on airline on-time performance to determine performance of each airline, relative to the average delay at a given airport.  The premise was carriers such as Hawaiian Airlines, predominately operating in great weather, are having their on-time performance grades skewed up.  Alternatively, carriers operating in congested and/or foul-weather destinations (Philadelphia, New York, Boston) are having their on-time performance under-reported.  The idea is to index a carrier’s performance relative to the system’s performance as a whole.

After loading nearly 9 Million flights into PowerPivot from the BTS site I constructed DAX formulas for the average delay for the current context filters, across all carriers.  Because I know that I will use this in the context of an Airport, I have labeled it Airport_Avg_Delay. 

The average delay for a given carrier (and/or other context filters) is a simple AVERAGE().

Dividing the AVERAGE by the Airport_Avg_Delay, formatting the result as a percent yields and conditionally formatting the table yields the PivotTable below.  Additionally, I have filtered for only May, 2010 data and only selected airports, for this example.

As I have pointed out earlier, Conditional Formatting is a nice tool for getting a rapid visualization from a table of values.  It can make identification of the best and worst performers easy in a casual glance.

Outliers Matter

The important thing to remember, with out of the box conditional formatting, is that outliers matter.  As more extreme data points are gathered, the degree to which other observations are shaded as ‘hotter reds’ or ‘colder blues’ changes.  For instance, if I were to eliminate Anchorage (ANC), with the American Airlines 375% performance relative to average OR Philadelphia (PHL) with 428% for Skywest, the entire color format of the chart would change.

Another example of why outliers matter is the table below.  I have filtered down to May 10, 2010, which was a bad day to fly into Philadelphia on Skywest or Chicago O’Hare on Pinnacle.  Their performance relative to the average of over 1490% and 2254% skew the entire table.  There is not another red-shaded value of less than 4 times worse than average.  Even performance at double the average is visually indicated as ‘normal’.  Because the range of normal changes with the introduction of the outlier.