Ordering data using the Show Top/Bottom feature

You can do more in the Visual Report Builder than create analyses that trend over time. For example, you can build a report to show how valuable your acquisition and marketing channels are, but you can also build a report that shows only the top five performers. Similarly, you can refocus your marketing efforts by creating a report that shows you what states make up the top 10% of your revenue.

Did you know… …That there’s a Training Video for this topic? Click here to check it out!

This kind of sorting and ordering of data can be done in reports that use both a Group By and a Time Interval of None. When both of these elements are in a report, the Show Top/Bottom feature will display above the chart preview. This feature allows you to see the top (highest to lowest) and bottom (lowest to highest) data points based on the parameters you set.

Show Top/Bottom feature in the Visual Report Builder.

How do I use this?

After clicking the Show Top/Bottom link, a window will display where you can set the display and sorting parameters. The number in the textbox can either be a whole number (5) or a percentage (5%). If you want to sort by a percentage, you must include the % character. Next, you can choose to sort the report either by the metric OR by the grouping.

For example, if we wanted to display the five referral sources that brought in the most revenue, this is how we’d do it:

  1. To start, we added the Revenue metric to the report.

  2. Next, we added a Group By to segment the metric by referral source.

  3. Then we set the Time Interval to None.

  4. In the Show Top/Bottom settings, we set the display to 5 so only the referral sources with the top five total revenue amounts would be included in the report.

Note: Because the report doesn’t have a Time Interval, the values - in our case, the top five referral sources - can change over time. If one referral source surpasses another in terms of revenue, the order in which the sources display will change.

What about using multiple metrics?

Using this feature gets complicated when there’s more than one metric in a report because each metric can only be sorted by itself or by one of the groupings.

Let’s say we built a report with both the Revenue and Number of orders metrics, grouped by referral source. Revenue can only be sorted by Revenue or referral source and Number of orders can only be sorted by Number of orders or referral source.

This means that while we can show the revenue from only the top 5 revenue generating referral sources, we cannot show the number of orders also by the top 5 revenue generating referral sources. Simply put: when there are multiple metrics, the best bet is to sort each metric by the grouping.

Here’s an example of a chart where we sorted the Revenue metric by itself instead of by the grouping. As you can see, not sorting the metric by the grouping created a strange (and ultimately unhelpful) report:

Strange and unhelpful report results.

If we had sorted both metrics by the grouping, the chart would look like this:

Sorting both metrics by the grouping.

How are values sorted by default?

When only one metric is included in a report with a Group by and a Time Interval of None, the default ordering in the Visual Report Builder is to show the top values based on the metric. In this instance, the Show Top/Bottom feature may not be necessary if this serves your needs.

In this example, we’re looking at how many opportunities our sales reps have closed. This table is automatically sorted from highest to lowest based on the metric, in this case Won Opportunities.

Ordering by the metric.

However, when a second metric is added, the default is to order the top based on the grouping. As metrics and groupings are added, the default sorting will be based on the first grouping, then the second grouping, and so on.

Ordering by the grouping.

Wrapping up

We mentioned it at the beginning of the article, but we’ll say it again: while we covered some basic examples, this feature has a lot of interesting uses.

Think about our previous sales rep and opportunities example. Removing the Time Interval, applying a Group By, and sorting the data based on the grouping allowed us to get a detailed picture of each rep’s number of won opportunities. Additionally, using the Show Top/Bottom feature let us discover who the top performers are.