Sorting a table visualization

Contents[Hide]

1. Overview

This article shows you how to use the various sorting and reordering 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 (e.g., Display As Flat Table option). 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 (autogenerated or checked out), 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 are now swapped as well as the grouping of the hierarchy values.

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

Switch back to Edit mode and open the Data Binding Panel for the table. You'll see that the order of the hierarchies in the ROWS field has been changed as well. This is possible because the metric set is checked out.

Note
What if the metric set was checked in? In this case, any reordering done in View mode would be reverted when switching back to Edit mode.

Data Binding Panel for the table: Row hierarchy order has changed
Data Binding Panel for the table: Row hierarchy order has changed

Switch to View mode and 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'll 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 but within each grouping only (e.g., Color = White). 

Sort descending on first column and also second column
Sort descending on first column and also second column

2.2. Regular columns

Regular columns in View mode can be reordered by dragging column headers as usual. 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 (if it is autogenerated or checked out).

As an example, start with the metric set from the previous example with two hierarchies. Create a new dashboard and drag the metric to the canvas.

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

Change the sort order for the column to Descending. The second column now has the highest sorting priority and its values are all sorted in descending order regardless of the column position.

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

Each time you sort on a column this way, that column will be given the highest sorting priority.

Next, switch to Edit mode and open the Data Binding Panel for the table. You'll see that the order of the hierarchies in the ROWS field has changed so that the hierarchy corresponding to the second column is first.

Color is now the first row hierarchy of the checked out metric set
Color is now the first row hierarchy of the checked out metric set

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 just by dragging column headers as usual. This will not affect the ordering of the MEASURES field 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 (if it is autogenerated or checked out).

As an example, drag a row hierarchy and two measures from the Explore window to the dashboard canvas.

Table with a row hierarchy and two measures
Table with a row hierarchy and two measures

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

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

In the column menu, click Descending. The second measure column now has the highest sorting priority and its values are all sorted in descending order.

Switch to Edit mode and open the Data Binding Panel for the table. You'll see that the order of the measures in the metric set has changed; the second measure is now listed first.

The SalesLastYear measure is sorted in descending order and is listed first in the Data Binding Panel
The SalesLastYear measure is sorted in descending order and is listed first in the Data Binding Panel

The measure column that you sorted on most recently gets the highest sorting priority. This is better illustrated with a different example in which one of the measures has integer values, allowing for the possibility of rows having the same measure value. The following figure shows the design of a table displaying 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.

4. Sort on a measure and a hierarchy

Sorting on a measure column first and followed by sorting on a hierarchy column, is no different than sorting multiple measures or multiple hierarchies displayed as regular columns. You can mix and match such columns for sorting purposes. Whichever measure or hierarchy column you sorted most recently gets the highest sorting priority. Previous sort order settings are retained but applied at a lower priority.

As an example, set up a table visualization by dragging two hierarchies (Name, Color) and a measure (ListPrice) to the dashboard canvas.

Open the Data Binding Panel for the table and click Edit. Locate the Shown Totals dropdown and set it to None.

Go to the toolbar and select Display As Flat Table.

Set up a table with two hierarchies and a measure
Set up a table with two hierarchies and a measure

Switch to View mode and use the column menu to sort the ListPrice measure in descending order. This gives the ListPrice column the highest sorting priority.

Sort ListPrice measure in descending order
Sort ListPrice measure in descending order

Then sort the Color hierarchy column in ascending order. This gives the Color column the highest sorting priority.

Sort Color hierarchy in ascending order
Sort Color hierarchy in ascending order

Scroll down in the table and look for rows where the Color hierarchy values are the same. Observe that the corresponding ListPrice values are still sorted in descending order for those rows. This shows that the previous sort order settings for the measure were retained but applied at a lower priority.

5. Sort using the context menu

You can also apply sorting from the context menu when you right-click (or long-tap if using a tablet) over a column header or table cell. This option gives you the flexibility of applying sorting to a subset of rows instead of the entire table.

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 (or long-tap) 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 submenu 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 (or long-tap if using a tablet) 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. 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