Sorting a table visualization

Contents[Hide]

1. Overview

This article shows you how to use the various sorting features of a table visualization.

2. Sort on multiple hierarchies

When you use a table visualization to display multiple row hierarchies, the corresponding columns can be formatted as either row header columns or regular columns. By default, row header column cells are shown with a grey background whereas regular column cells use alternating but lighter colors for the background.

2.1. Row header columns

In the case of row header columns, the sorting priority is determined by the left-to-right order of the columns. This is the same as the order of the row hierarchies in the underlying metric set, and the same as the grouping order of the hierarchies.

As an example, create a new metric set with two row hierarchies.

Create a metric set with two row hierarchies: Name and Color
Create a metric set with two row hierarchies: Name and Color

Create a new dashboard and drag the metric set from the Explore window to the canvas.

Drag metric set to dashboard canvas
Drag metric set to dashboard canvas

Switch to View mode and reorder the table columns by dragging the header of the second column onto the header of the first column.

Reorder table columns by dragging column header
Reorder table columns by dragging column header

The columns, as well as the grouping of the hierarchy values, are now swapped.

Columns are reordered and the grouping is by Color first
Columns are reordered and the grouping is by Color first

Note
If the underlying metric set is checked out to you, this change will be saved on the metric set. Open the Data Analysis Panel in Edit mode to see this.

Hover over the first column header. Click the A-Z icon on the right to open the Sort/Filter column menu.

Click the A-Z icon to open the Sort/Filter column menu
Click the A-Z icon to open the Sort/Filter column menu

In the column menu, click Descending in order to sort the first column in descending order. Do the same for the second column.

You will see the values in the first column are now sorted in descending order. In the second column, the values are also sorted in descending order within each grouping. 

Sort descending on both columns
Sort descending on both columns

Another way to re-order the columns is via the Data Analysis Panel. Drag the Name column over the Color column to switch them back.

Use the Data Analysis Panel to switch the columns back
Use the Data Analysis Panel to switch the columns back

You will see the Name column is now first again, with the values still sorted in descending order. In the second column, the Color values are also sorted in descending order within each grouping. 

2.2. Regular columns

Regular columns can also be reordered by dragging column headers in View mode. This will not affect the order of the row hierarchies in the underlying metric set's ROWS field. However, using the Sort/Filter column menu to sort a regular column will give that column the highest sorting priority and cause the corresponding row hierarchy to be reordered first in the underlying metric set.

As an example, start with the dashboard from the previous example with two hierarchies.

Select the table visualization on the canvas, go to the toolbar, and then click Display As Flat Table.

Click Display As Flat Table
Click Display As Flat Table

The row hierarchies are now displayed as regular columns instead of row headers.

Row hierarchies displayed as regular columns
Row hierarchies displayed as regular columns

Switch to View mode and hover over the header of the second column. Click the A-Z icon to open the Sort/Filter column menu.

Open the Sort/Filter column menu for the second column
Open the Sort/Filter column menu for the second column

Even though Descending is already selected, click on it. The second column now has the highest sorting priority and its values are all sorted in descending order regardless of the column position. This is equivalent to reordering the row header columns and changes the order of row hierarchies on the metric set.

Color column is sorted in descending order
Color column is sorted in descending order

3. Sort on multiple measures

Sorting multiple measures is similar to sorting multiple row hierarchies that are displayed as regular columns.

Measure columns can be reordered by dragging column headers as usual. This will not affect the ordering of the measures in the underlying metric set. However, using the Sort/Filter column menu to sort a measure column will give that column the highest sorting priority and cause the corresponding measure to be reordered first in the underlying metric set.

As an example, create the following table using the Name, SafetyStockLevel, and ListPrice columns from [Production].[Product].

Table showing Name, SafetyStockLevel, and ListPrice
Table showing Name, SafetyStockLevel, and ListPrice

Switch to View mode and use the column menu to sort ListPrice in Descending order. Then sort SafetyStockLevel in Ascending order.

Sort ListPrice in descending order and then sort SafetyStockLevel in ascending order
Sort ListPrice in descending order and then sort SafetyStockLevel in ascending order

Observe that the SafetyStockLevel measure values are all in ascending order and the corresponding column has the highest sorting priority because it was sorted most recently. The ListPrice measure column has the next highest priority and its values are still sorted in descending order but only within rows with the same SafetyStockLevel value.

Note
Unlike hierarchies, changing the measure column order on the Data Analysis Panel will not change the column order in the table. You can change the column order in View mode, or using up and down arrows in the main properties.

4. Sort on a measure and a hierarchy

You can mix and match sorting on hierarchies and measures. When the hierarchy is presented as a regular column (such as in a flat table), the result will be the same as sorting multiple measures. However, the grouping created by sorting multiple hierarchies will be maintained even when sorting by a measure. In such a case, sorting the measure will actually sort the groups by their aggregator (e.g. Totals) and separately sort the values inside each group.

As an example, create the following table using the Color, Name, and Weight columns from [Production].[Product]. Display it as a flat table.

Flat table showing Color, Name, and Weight
Flat table showing Color, Name, and Weight

By default, the Color and Name columns are sorted in ascending order, while the Weight column does not have sorting applied.

In View mode, open the Sort dialog for the Weight column. The dialog will indicate which hierarchy column is responsible for the top level grouping.

You can change the hierarchy column responsible for the top level grouping by displaying the table with row headers and following Section 2.1, or by sorting by another hierarchy (in this example, the Name column). 

Click Change to display the table with row headers.

Sort dialog for the Weight column
Sort dialog for the Weight column

Change the sort on the Weight column to Ascending. You will see that the groups (the Color column) have been reordered based on their totals, and the rows in each group (the Name column) have been reordered based on their individual values.

Ascending sorting on the measure
Ascending sorting on the measure

Note
The aggregator in calculated measures (formulas) is None by default. When you sort by a measure without an aggregator, the rows in each group will be sorted but the groups themselves will remain unsorted. You will have to set an aggregator for the sort to also affect the group order.

5. Sort using the context menu

You can apply sorting from the context menu when you right-click on a table cell. This option gives you the flexibility of applying sorting to a subset of rows instead of the entire table. When the column is sorted or filtered, the direction of the sort, and a filter icon will be present in the column header when you move the mouse over it.

As an example, set up a table visualization with a product hierarchy, a date hierarchy, and one measure.

Table visualization with a product hierarchy, a date hierarchy, and one measure
Table visualization with a product hierarchy, a date hierarchy, and one measure

Switch to View mode and then right-click over one of the measure subtotal cells for a product category. From the context menu, click Sort.

Click Sort from the context menu
Click Sort from the context menu

A sub-menu appears and asks you to choose the specific measure or hierarchy to sort on. Click the measure (OrderQty).

Sort on OrderQty
Sort on OrderQty

Click Descending.

Sort descending
Sort descending

The product categories are now sorted in descending order according to their corresponding subtotal measure values. The date rows within each product category are unchanged.

Subtotal rows are sorted in descending order
Subtotal rows are sorted in descending order

Next, right-click on a measure cell corresponding to one of the date rows.

Sort on a measure cell corresponding to a date row
Sort on a measure cell corresponding to a date row

Click Sort, select OrderQty, and then click Ascending.

Date rows within the product category are now sorted in ascending order according to their measure values. The order of product categories is unchanged.

Date rows within the product category are sorted in ascending order
Date rows within the product category are sorted in ascending order

6. Notes

  • These sorting features are not available in the Metric Set Designer when using a Dynamic Hierarchy. However, you can use them when in the View mode of a dashboard using this metric set.
  • Changing the hierarchy order (for example, in View mode, drag the Year row header over the Product Category row header) will reset your sorting selection. 

7. See also

 

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

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