Magento Business Intelligence Help Center

Ordering data using the Show Top/Bottom feature

BROUGHT TO YOU BY

Avatar

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.
Click for a closer look!

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:


Click for a closer look!

  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 that 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.
Click for a closer look!

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

Sorting both metrics by the grouping.
Click for a closer look!

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.
Click for a closer look!

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.
Click for a closer look!

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.

Was this article helpful?
0 out of 0 found this helpful

Comments

  • Avatar
    Mark Lurie

    I understand why we can't show only the top X records by multiple metrics.  However, we should be able to sort multiple metrics by another metric.  For example, in the case above, I might want to sort both Revenue and Number of Orders by Revenue.  That should be possible, but from this article it appears it is not.  Is there a feature that enables this that I'm not aware of?

    Thanks!

    Mark

  • Avatar
    Maya Zito
    Hey Mark!
     
    Thanks for reaching out. You are correct, at this time, there is no feature that enables the type of segmentation you are referring to. This is great feedback and something I'm happy to pass along to our product management team so we can get this on our product roadmap. 
     
    Thanks,
    Maya
Powered by Zendesk