Define custom measure sorting
This article shows you how to configure measure sorting by specifying an axis hierarchy to sort along and the hierarchy level. A typical scenario is sorting sales (measure) figures for each product (axis hierarchy) from largest to smallest.
You can configure measure sorting from the Metric Set Designer screen or while designing a dashboard via the Data Binding Panel.
2. Defining custom measure sorting
For this example, create or edit a metric set that is configured with 1 measure and 1 hierarchy on the ROWS axis.
From the toolbar, re-visualize the metric set as a bar chart and observe that the OrderQty measure values are unsorted along the Product hierarchy.
In the Metric Set panel, click the Edit icon of the OrderQty measure.
In the Configure Metric Set Element dialog, scroll down and click Define custom measure sorting.
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 on the primary axis you want to sort the measure along, such as Product.
- Select the Axis Hierarchy Level. Choose a corresponding hierarchy level, such as Product Category.
Click Submit to save your changes.
Observe that the OrderQty values are now sorted from largest to smallest along the Product (ROWS) axis of the bar chart.
3. Additional hierarchies on opposite axis
As an extension of the above example, consider adding to the metric set a second hierarchy (OrderDate) on the opposite axis (COLUMNS).
In this case, when configuring the measure sorting, you need to choose the hierarchy to sort along (e.g., Product) and you must also choose specific members of hierarchies on the opposite axis (e.g., OrderDate).
The resulting metric set shows measure values sorted in descending order for the Year 2007.
4. See also
- Define custom hierarchy sorting
- Create a metric set and add a filter
- Automatic joins and hierarchies