Define custom measure sorting

Contents[Hide]

1. Overview

This article shows you how to sort the data in your metric set by measure values using the dialog available from the Data Analysis Panel. For example, you could sort your Sales measure's figures for each Product from largest to smallest.

This dialog allows you to sort one specific hierarchy level by a measure, and when applicable you can set the opposite axis members from a dropdown.

Tip
You can also sort your data from the Sort & Filter popup in the Data Analysis Panel or from your visualization, and those options allow you to sort more than one hierarchy level at a time by a measure. See Metric set analysis tools for details.

2. Defining custom measure sorting

As an example, we have a metric set configured with one measure OrderQty, and a Product hierarchy under Rows.

From the toolbar, we re-visualized the metric set as a bar chart and observe that the OrderQty measure values are unsorted along the Product hierarchy.

Metric set with unsorted measure
Metric set with unsorted measure

In the Data Analysis Panel, click on the OrderQty measure to edit it.

Click to edit measure
Click to edit measure

In the Configure Element dialog that opens, scroll down and click Define custom measure sorting.

Configure element dialog
Configure element dialog

In the Configure Measure Sorting dialog, set the following options:

  • Choose a Sort Direction. For this example, set it to Descending so that measure values will go from largest to smallest.
  • Select the Axis Hierarchy. This is the hierarchy values your want to be sorted by your measure, such as Product.
  • Select the Axis Hierarchy Level. Choose a which level of the hierarchy to sort by the measure when there are multiple, such as Product Category.
  • If you need to sort all rows by this measure independently of any groupings (including totals), select Ignore Hierarchy Groupings.

Configure Measure Sorting dialog
Configure Measure Sorting dialog

Click the Submit button at the bottom of the dialogs to save your changes.

Observe that the OrderQty values for the Product Category data points in the chart are now sorted from largest to smallest.

Product data points sorted in descending order by OrderQty
Product data points sorted in descending order by OrderQty

3. Opposite axis hierarchies

When sorting the rows or data points of your metric set by a measure, for example, you might have a hierarchy under Columns in the Data Analysis Panel that splits your measure values into multiple columns or series. In this case, you would need to decide which column or series to sort by.

As an extension of the above example, consider a second hierarchy OrderDate added to the metric set on the opposite axis from Rows, which is Columns (Series). This produces a separate data point series for each year in a bar chart.

Measure values split up into multiple columns/series
Measure values split up into multiple columns/series

Now when configuring the measure sorting you also need to choose which OrderDate (year) values to sort by under Opposite axis members.

Additionally an opposite axis member
Additionally an opposite axis member

Tip
You can select (All) to sort by the measure value total for all opposite axis members even if those totals are not being visualized directly. For example: the total of all years for each product category.

The resulting metric set shows measure values sorted in descending order for the selected year.

Measure values for 2011 sorted in descending order
Measure values for 2011 sorted in descending order

4. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri