SQL Business Intelligence Professional

Demonstrating PowerPivot for SharePoint to a potential client, I was given the harmless request “can you sort the results in the chart?”.  I can see how this would be needed in statistical process analysis, or in simply creating a dashboard to reflect top (or bottom) performers by rank or percentage.

The examples for this post use data from the Department of Transportation on flight arrivals and departures, that will be the topic of a more involved post on getting started with PowerPivot.

As you can see in the screenshot below, the chart in the upper right corner of the dashboard represents the percentage of Huntsville departing flights delayed, by destination airport code.  Useful, because by default the order of the x-axis items is in dimension order which is alphabetical in our case.  That is, ATL (Atlanta Hartsfield-Jackson) is before CLT (Charlotte Douglass).

However, our client may want to see this chart in order of the measure value, like the Delay % by Carrier chart to the left.

The key to this little trick is each of our PivotTable charts has behind it a sheet of data.  In the case of this four-chart layout, the charts are numbered top to bottom, left to right.  So, Delay % by Carrier is Chart 1, Delay by Departure Time Block is Chart 2, and our target chart Delay % by Departure City is Chart 3.  The figure below has an arrow from each of the charts to their corresponding data sheet.

Data Sort and Rank Options

Navigating to the Data sheet behind Chart 3, I can quickly sort the charted values and therefore the chart.  Selecting the Destination Airport column, and right-clicking to get a context menu, I can use the menu selections below.

You will notice the default is data source order, hence the original alphabetic order.  We are going to change this to Descending order by the measure value, PercentDelay.

And BAM!  Chart 3 in the Dashboard sheet is now ordered by the measure.  Still showing Delta’s hub and worldwide headquarters Atlanta is worst (for the parameters of this report) but now Houston’s George Bush and Detroit Wayne County have moved Charlotte to fourth, in order order percentage of flights delayed.

Pulling Rank

So the initial steps cover Sorting the chart data.  Now let’s add ranking to the chart.  In the current form, the chart has 11 airports in the x-axis.  Our client just wants to see the top 10, worst airports by percent of flights delayed.  No problem.

From the same data sheet context menu, instead of Sort, we choose Filter and then Top 10.  The result should be a dialog box similar to the one below.  We can actually use this to filter absolute count (top/bottom n), percent (top/bottom 10%) or use an aggregate to filter members from the chart.  In our example, I am leaving the default of 10.

Pressing OK and revisiting the Dashboard sheet, and I see the Chart now has dropped CVG (Cincinnati), to reflect the top 10 airports by percent delay.