Metric set analysis tools

Contents[Hide]

1. Overview

A metric set allows you to select and analyze a set of data. It's displayed in your choice of data visualization while you work with one, but can be reused in different visualizations and by other people you share it with.

Metric sets power all visualizations. You can work with metric sets one at a time in the full screen editor, or combine them in a dashboard or another view to edit and analyze them together. This article introduces metric set functions as seen when editing one full screen, but they are also available when working with metric sets on a dashboard or another view.

The full set of analysis capabilities are available while editing a metric set that is checked out to you, while anyone viewing the metric set is able to access powerful metric set features such as drill down, sorting, and grouping.

Tip
When viewing a checked-in dashboard as a power user or above, you can right-click a visualization and choose Data Tools, then Analyze Data to open a copy of that metric set for editing.

Related videos:

Walkthrough: Create a metric set and add a filter

2. Selecting and grouping data

2.1. New metric set

To create a metric set in the full-screen editor, choose Business in the main menu and then click Create.

Start by choosing your first data source.

Choose your data
Choose your data

This can be any data found under a data connector or data cube someone has already created, or a file from your computer like a spreadsheet.

Drag it onto the canvas, or choose Use an existing data structure to locate it. You can also create a new data connector here if needed.

A new metric set with a measure
A new metric set with a measure

Tip
Create and edit metric sets directly on a dashboard or other view by dragging data onto its canvas, then using the same Data Analysis Panel.

A table visualization now displays whatever data you chose with the Data Analysis Panel to the left.

2.2. Adding more data

The Data Analysis Panel is central to choosing and analyzing data. Regardless of which visualization you use, you can group and filter your data by placing it under one of these same four options:

  • Measures: numeric values that can be grouped and filtered
  • Rows: group by this data into a row for each value
  • Slicers: filter the data by the selected values
  • Columns: group by this data into a column for each value

You can find more details and examples in Slicers versus columns and rows.

To see more data, drag it from the Explore window, or you can click to add more data from your current data source.

Add more data
Add more data

Tip
In the Explore window, different icons indicate whether data is has been categorized as a measure (numeric data) or a hierarchy (usually non-numeric data). If you are working with data from a data connector directly, you can use numeric data as either a measure or hierarchy regardless, or you can right-click and choose Change Category to switch between them. (Database primary keys with key icons are typically numeric but used as hierarchies.)

When dragging, you can drop data directly where you want it in the Data Analysis Panel, or onto the blue highlighted area of the visualization if you're not sure where to drop it. In a full screen metric set, you can also access the Assistant from the toolbar and type what you want to see.

By placing data on Rows or Columns, you are grouping by those values in the order they are placed. For example, place a time dimension hierarchy on Rows to see sales numbers grouped by year, even if the data source contains many rows for individual dates.

Grouping by country then by date at the year level
Grouping by country then by date at the year level

Tip
You can drag data from another table or data source in the Explore window and it will be automatically joined. This uses database relationships for tables from the same database, or user-defined relationships which can work even for different data sources.

If you click to add the special option <Row Number>, the data will not be grouped and all original rows will be displayed unaggregated and unsorted as 'raw data'. There are various limitations with raw data mode, but in return this allows you to view the underlying data unmodified. This option is also initially selected when you select or drag an entire table or spreadsheet as the data for your metric set.

A spreadsheet with <Row Number> added under Rows
A spreadsheet with <Row Number> added under Rows

2.3. Moving, reordering, and removing

To move data between Rows, Slicers, and Columns, drag it from where it is placed already in the Data Analysis Panel to the new area where the label drop a hierarchy appears.

To change the order of multiple items placed under the same heading, drag a lower item and drop it where you want it located.

Dragging and dropping to reorder
Dragging and dropping to reorder

The order of hierarchies determines their grouping order. The order of measures determines their sorting priority when sorting by more than one measure.

Click the delete button to the right of the hierarchy or measure to remove it from the metric set.

2.4. Replacing with a hierarchy

If you are working with data directly from a database, spreadsheet, or another regular table of data that was not prepared as a cube, you can replace a regular column with a hierarchy from the Explore window.

  • Date/time columns can be expanded in the Explore window to find time dimension hierarchies and attributes, which you can drag onto the Data Analysis Panel or your visualization to group by time periods such as Year, drill up & down, and more. You can expand a hierarchy and drag a specific level you want to group by, or change the level later.
    Expand to find time dimensions
    Expand to find time dimensions
  • If you already added data related to a hierarchy that was created or if you dragged date/time data without a time dimension, drag the hierarchy from the Hierarchies or Time Dimensions folder on top of it in the Data Analysis Panel. You can also drop a simple column displaying friendlier names on top of a column of IDs or keys. See Automatic joins and hierarchies for details.
    Locate a predefined hierarchy
    Locate a predefined hierarchy

3. Visualizations

A metric set's data is always shown in a visualization that you can change and customize. When you edit a metric set full screen, this visualization is the default visualization used when the metric set is first added to a dashboard or other view, but you can also use it just for your own analysis.

Note
The metric set defines all of the data that's displayed and its settings such as initial filtering and sorting. Each visualization is separate so it can be changed and customized when reusing a metric set.

If you didn't choose a particular visualization yet or customize it, a visualization will be generated for you automatically each time you add more data to a new metric set. For example, a measure and a time dimension hierarchy will be automatically visualized as a line chart, which is best for seeing the trending of a measure over time.

A measure and time dimension hierarchy visualized as a line chart
A measure and time dimension hierarchy visualized as a line chart

At any time, you can change to the visualization of your choice by clicking Re-Visualize in the toolbar and choosing an option. Once you have chosen a visualization yourself, customized it, or saved and re-opened this metric set, it will not be changed automatically.

Click Re-Visualize to change the visualization
Click Re-Visualize to change the visualization

You can customize a particular visualization in a number of ways:

  • Drop zones may appear over your visualization when dragging data from the Explore window, allowing you to choose how it will be visualized. For example, you can decide whether to display a hierarchy as a regular column, a row header column, or as a row of column headers in a table.
    Choices for visualizing a hierarchy in a table visualization
    Choices for visualizing a hierarchy in a table visualization
  • Similarly, you can click the Visualization button in the Data Analysis Panel of any visualization and drag data from the Explore window or click to add data directly under a visualization option, such as Tooltip for displaying additional details in a popup when hovered or long-tapped.
    Visualizing data in a tooltip
    Visualizing data in a tooltip
    See Setting up the visualization and Visualization tab examples for more details and examples.

  • Common visualization options can be found in the toolbar under Contextual section, and in the right-click context menu, such as changing a vertical bar (column) chart to a horizontal bar chart.
    Visualization options in the toolbar
    Visualization options in the toolbar
  • The full set of visualization settings are found in the Properties window.

When a metric set is added to a dashboard or another view, the default visualization from the full screen editor is used as the template for a new visualization. The metric set is reusable and each visualization is separate, so you and other people you share the metric set with can customize it and visualize it differently.

Tip
If you want to reuse a visualization in multiple places and be able to make changes to all of them at once, you can create a dashboard for that visualization and drag it onto other dashboards and views.

4. Sorting and filtering

To quickly filter or sort data while editing, use the sort & filter icon shown for the values you want to sort by or filter.

For example, you can filter the range of dates shown on a chart, or sort a table's rows by a measure.

The Sort & Filter popup shown for Country
The Sort & Filter popup shown for Country

Note
For measures, this quick sorting option reorders the rows of data by its values. If you want to reorder the columns by this measure instead, use the right-click sorting options or set up custom measure sorting.

This is the same popup shown when sorting a table visualization from one of its column headers, and it can contain options such as changing the grouping of the data.

You can also right-click (or long-tap) data directly in the visualization and access sorting and filtering options from the context menu.

Sorting and filtering from the context menu
Sorting and filtering from the context menu

If you choose Sort, a list of measures or hierarchies may appear if there is more than one related to what you clicked. Choose what values should be used to sort the rows (or columns), then choose the sort order. For an example of sorting using the context menu, see Sorting a table visualization.

Context menu sorting options
Context menu sorting options

If there are multiple columns (or series) of data that can be sorted, additional options appear allowing you to sort the Columns Ascending or Columns Descending instead of sorting the rows. The More... option brings up the same sort & filter popup shown above with options to change how the data is grouped.

If you choose Filter, you can choose to keep only or exclude the data you right-clicked on. This can apply to multiple rows, columns, or data points at once if you clicked or dragged to select multiple. You can also choose the name of a hierarchy listed to choose some of its values to filter by, or choose Clear All to clear any previous filtering (if applicable).

Context menu filter options
Context menu filter options

For an example of excluding selected data, see Exclude data points from the visualization.

These context menu options are available even when you or others are viewing this metric set after it's been added to a dashboard and shared. By sorting and filtering with the metric set checked out to you, you can determine the initial settings when it's viewed the first time.

More advanced options are available from the Data Analysis Panel as described in Define custom hierarchy sortingDefine custom measure sorting, and Displaying top/bottom records.

5. Changing levels and drilling down

When displaying data from a multi-level hierarchy, you can change which levels are displayed, or drill down to see the values in the level below that make up a value.

Context menu options
Context menu options

To quickly change the level, right-click (or long-tap) anywhere on the visualization and choose Change Level. (If there are multiple hierarchies to choose from, choose one from the list that appears next.)

Change level options
Change level options

Choose a level to change the level for the entire current set of data. 

Many visualizations can display multiple levels at once, including tables and relationship diagrams. To select a range of levels instead of just one, choose Change Top Level and then a level, or Expand To Next Level to display both the current level and the next level down. When the main level is different than the top level, both levels and the levels in between will be displayed at once.

Table showing monthly data with Year as top level
Table showing monthly data with Year as top level

Since these are metric set settings, you can also set the Level and Top Level from the Data Analysis Panel by clicking to edit the hierarchy and expanding the Parameter Values section. You can also drag hierarchy levels onto your visualization from the Explore window as a shortcut.

Right-click (or long-top) data in a visualization and choose Drill Down to filter to that value and change the level of the hierarchy down. (If there are multiple hierarchies to choose from, choose one from the list that appears next.)

For example, drill down on 2022 to see the individual monthly values that make up that year.

Result of drilling down on 2022
Result of drilling down on 2022

To change the level back up and clear the filtering, choose Drill Up in the context menu.

Like sorting and filtering, these context menu options are available even when you or others are viewing this metric set after it's been added to a dashboard and shared. Changing the level and drilling down while the metric set is checked out to you determines the initial settings when it's viewed the first time.

6. Expanding and collapsing

When multiple hierarchies are added under Rows or Columns in the Data Analysis Panel, or if a multi-level hierarchy is added, you can right-click (or long-tap) values and choose Expand or Collapse. Some visualizations may also offer triangular expander buttons.

Expand & collapse options
Expand & collapse options

Expanding is similar to drilling down, except the other data remains displayed as before, with the values added from the level below or from the next hierarchy just for the expanded value.

Result of expanding 2023
Result of expanding 2023

Collapsing removes the values below just one value, leaving the rest of the data in place.

Result of collapsing North America
Result of collapsing North America

Use the Collapse All option to collapse all of the values of any hierarchy into a single total.

Collapse all option
Collapse all option

For example, you can collapse all individual years into a summary grouped by the remaining values.

Result of collapsing all years
Result of collapsing all years

Notice above how this is similar to collapsing all the individual values in the leftmost column.

Use Expand All to reverse this action.

Note
If you want to expand all of a hierarchy level's values at once to the next level of that hierarchy, e.g., expand all the years down to months, you can use the options under Change Level for this as shown in the previous section.

Unlike expanding and collapsing individual values, Collapse All is a metric set setting that is saved, and can also be found in the dialog for each hierarchy in the Data Analysis Panel by clicking its orange tile to edit it. For more details and examples of expanding and collapsing, see Expand and collapse hierarchy members.

7. Measure options

When numeric data is first added under Measures in the Data Analysis Panel, its values are grouped and normally summed, as indicated by the displayed text SUM.

The measure's aggregator
The measure's aggregator

To quickly change this aggregator to another one, click this indicator and choose another from the list. For example, you can see the average order quantity for each year instead of the sum.

You can add a measure multiple times: drag the same data from Explore or click to add it again under Measures. Each copy of the measure can be set to a different aggregator (you should edit their captions to distinguish between them).

Adding the same measure with different aggregators
Adding the same measure with different aggregators

To access the full set of measure options, click its green tile or pencil icon in the Data Analysis Panel to edit it.

Measure settings dialog
Measure settings dialog

The dialog includes settings such as:

  • CaptionDetermines how this measure is identified in visualizations.
  • Format: Determines how numeric data is formatted and displayed as text. See Formatting text for details on formatting.
  • Missing Data Rules: These settings determine if rows or columns of data should still be displayed if there is no corresponding measure value available, and whether to fill them in with replacement values. See Handling null data points for details.
  • Hidden: If you don't want to display the measure but only use it for a formula, sorting, or other metric set features, check this box to prevent the measure's values from being seen directly or exported.

Changing settings such as Caption and Format here take effect everywhere at once as long as you haven't customized a visualization setting on top of them. For example, changing the caption affects column headers and tooltips, and changing the format affects the cells of a table, chart labels, and tooltips. They will also take effect if you re-visualize the metric set.

8. Totals

Tables and some other visualizations have support for displaying totals. To quickly turn all totals on or off when they are available, choose Shown Totals in the toolbar when editing a full screen metric set and choose which totals you want to show.

Showing and hiding totals in the toolbar
Showing and hiding totals in the toolbar

You can also access this setting from the Data Analysis Panel by clicking the metric set's Edit icon.

Showing and hiding totals from the Data Analysis Panel
Showing and hiding totals from the Data Analysis Panel

Note
Charts would typically be disrupted by totals, so they are hidden by default by the property Hide Total Values in the Properties window for most chart types. This can be un-checked to Show or hide total values on a chart.

There are also separate settings available on each hierarchy, which you can access by clicking its orange tile in the Data Analysis Panel.

Showing and hiding totals for a hierarchy
Showing and hiding totals for a hierarchy

When changing the setting for a hierarchy, you can choose whether to include subtotals, the grand total, or both.

The main total for a hierarchy is its Grand Total. The figure below indicates the grand total for all countries plus each grand total for all years.

Grand totals for all countries and all years
Grand totals for all countries and all years

A hierarchy's Subtotals are displayed for its upper levels when displaying more than one or when it has been expanded to a lower level. The figure below indicates the subtotal for 2011 above months, and the subtotal for June, 2011 above its days because it has been expanded.

Subtotals for August, 2023 and the year 2023
Subtotals for August, 2023 and the year 2023

9. Data summary

When selecting rows or columns in a table, or data points in a chart, some basic statistics of that data will appear in the status bar: sum (SUM), average (AVG), minimum (MIN), maximum (MAX), the number of points (COUNT), and number of distinct values (DISTINCT).

If there are multiple measures, it will cycle through the statistics for each measure plus the statistics for all measure values mixed together. Hover over the data summary for a tooltip showing all of the statistics at once.

Status bar data summary for selected data in a table
Status bar data summary for selected data in a table

Note
Data summary is not available for waterfall and stacked chart types.

You can click and drag your mouse to select multiple points in a chart. Hold the Ctrl or Shift keys while doing this to select additional areas of data points. To select multiple rows or columns in a table, hold Ctrl while clicking them, or hold Shift to select a group of rows or columns in between the ones you click.

10. More tools

Metric sets include many more tools to help with your analysis, which are detailed in separate articles.

In the toolbar, you can find:

  • States for calling attention to some data, or displaying it differently based on conditions.
  • Period over period or slicer comparisons for viewing data from different time periods or filter values, or using it for state conditions or formulas.
  • Formulas for adding a new measure or an entirely new metric set based on a formula or script.
  • Contextual measures for inputting values to compare against your data, such as goals or targets.

The context menu provides you with additional options when you right-click or long-tap data:

The metric set has overall settings you can access by clicking the Edit icon in the Data Analysis Panel.

Edit metric set settings
Edit metric set settings

  • Transpose Result: A transposed metric set swaps the rows with the columns in the result, including displaying a different measure in each row instead of a separate column for each measure. This can be used to display a single column or chart series comparing different measure values, whereas each measure is normally a separate column or series.
    Transposed metric set with a row for each measure
    Transposed metric set with a row for each measure
  • Top/Bottom Items and Other Group: you can filter the metric to display only the top or bottom 'N' items of some number, or group together the smallest items into a single item (or use other criteria).

11. 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