Skip to content

10.2 Using Existing Metrics

Most of the metrics you need already exist: your administrator defines them in the data model, and colleagues add more. This topic shows where to find them, how to put them in a visual, and what happens when you change a metric column.

Finding Metrics

  • In the main window, the Metrics folder holds the project's metrics, sorted into subfolders. Select a metric to see its description on the right. Use the Search box at the top if you know part of the name.
  • In a workbook in design mode, the Data & Objects panel lists them under Global Objects → Metrics, and Search finds them by name – see 5.6 Side Panels.

Right-click a metric you use often → Add to favorites to find it under Favorites.

Adding a Metric to a Visual

In design mode:

  • Drag the metric onto a table, chart or gauge. Drop it on the values area, or on an empty spot of the sheet to start a new visual. Common sources to drag from include:
  • The Object Tree
  • Search results
  • The Element Structure view of another visual
  • Or right-click on the visual, select "Add" and choose from the Metric section:
  • Select "New" if you know the name and just want to type it in the formula editor, or
  • "Select metric" to browse the catalog for a metric, or
  • Select from the list of the most common metrics

The metric becomes a new column – in a table, a column of values; in a chart, a new series.

Aggregating a Table Column Directly

You can also drag a column from a table (under Data Sources) onto the values area. Datashop asks how to summarize it:

Column type Choices
Numbers SUM, COUNT, COUNT (distinct), More → AVG, MIN, MAX, VAR
Text and dates COUNT, COUNT (distinct)

The result is a column that belongs to this visual only. To share it with others, create a metric instead – see 10.3 Creating and Editing Metrics.

Suggestions After Dropping

When the metric is related to a date dimension, Datashop may offer a few common variants right after you drop it:

  • … year-to-date and … month-to-date;
  • … last year, … index to last year and … % of grand total, when the visual already shows the metric and the workbook filters on a date;
  • Add date filter on … → Year-to-date, Month-to-date, Week-to-date or Today, when the workbook has no date filter yet. This adds a date box to the Filter Panel.

Choose Insert … to keep the plain metric, or click elsewhere to close the menu. The variants are quick transformations – see 10.4 Quick Transformations.

The Metric and the Column

A visual doesn't change the project's metric. Each metric column has its own settings on top of the metric:

Set on the column (this visual only) Set on the metric (everywhere it's used)
Title, number format, rules, quick transformations, column filter, ignored filters, actions, visibility Name, title, description, aggregation or expression, metric filter, default number format, budget flag

To change the column, double-click it in the Element Structure panel. The dialog shows the column's formula, format, transformation and filter. Its Advanced ... button gives the metric's advanced settings, for this column only – see 10.3 Creating and Editing Metrics.

To change the metric itself, edit it from the main window (Datashop Expert).

Removing and Reordering Columns

  • Remove: right-click the column in the Element Structure panel → Delete, or right-click a value in the table → Delete.
  • Reorder: drag the column up or down in the Element Structure panel. Calculations that refer to columns by number (!C1, !C2 …) are renumbered automatically.

Tips – Using Existing Metrics

  • Read the metric's description before using it – two metrics with similar names can differ, e.g. including or excluding credit notes.
  • Drop the same metric twice to compare it with itself, e.g. this year next to last year, by adding a "Last year" transform to the second copy column

Common Issues – Using Existing Metrics

Problem: A metric shows the same value on every row. - Solution: The metric isn't related to the dimension in the rows, or it is set to ignore it. Pick a metric that belongs to the same data, or ask your administrator.

Problem: Dragging a table column only offers COUNT. - Solution: The column isn't numeric. Only number columns can be summed or averaged.