Using a formula visualization

Contents[Hide]

1. Overview

Formula visualizations allow you to use data from one or more visualizations to produce a new metric set (called a formula metric set) in a new visualization. These can be useful for a variety of analysis tasks, and can be created without leaving the editor of your dashboard or other view.

Related video: Formulas

2. Creating a formula visualization

Formulas used to create a formula visualization are the same as formulas used when adding a formula measure, except the result is displayed in a separate metric set.

As a simple example, suppose you have a bar chart on a dashboard that shows the List Price of each product, and you want to display the Average List Price in a data label.

A bar chart
A bar chart

In the toolbar, click Data Visualization and select Formula Visualization under Formula.

Select Formula Visualization from the toolbar
Select Formula Visualization from the toolbar

Enter a formula in the formula bar. In our example, we will type the first letter of the formula function AVG and select it from the list of available functions.

Select AVG function
Select AVG function

Click a measure value on your dashboard to insert that measure into your formula. This can be from any visualization, and its placeholder in the formula will include the visualization and metric set names.

Alternatively, you can enter the measure placeholder yourself, beginning with a dollar sign ($), then choosing the visualization name.

Select chart1
Select chart1

Type a period character (.) and then from the menu, select the metric set from the list of metric sets on that visualization (in some cases there may be multiple).

Select Metric Set 1
Select Metric Set 1

Type a period character (.) and then from the menu, select the measure from the list of available measures.

Select list_price
Select list_price

Type a closing parenthesis to complete the formula and press Enter or click apply.

Type closing bracket
Type closing bracket

The result of this formula is a new metric set displayed in a new visualization, which is initially a table.

In our example, a single measure value in a single row is included, because that is all that is output by the AVG formula we entered.

Result of the formula in a new table visualization
Result of the formula in a new table visualization

You can choose to re-visualize this new metric set. In our case, with the table selected, we will choose Re-Visualize in the toolbar and then Data Label. You might then want to choose Components and then Label, and set its text to "Average:" to display to the left of the data label.

Result of the formula in a data label
Result of the formula in a data label

Tip
Once the formula metric set has been created, it is safe to delete the original visualization and metric set from your dashboard or view, if you choose. The original metric set is used only to define the formula, and the new metric set is independent.

3. Combining data from multiple visualizations

Formula metric sets can also be created based on the data from multiple metric sets. Normally, this will only make sense when those metric sets are using the same user hierarchy or a time dimension hierarchy — the rows will be matched up when possible, and the data combined.

As an example, suppose we have two line charts on a dashboard: one displaying revenue over time from a database, and another with campaign spending over time from an Excel spreadsheet. We have dragged the same default time dimension hierarchy onto the dates in both charts.

Two visualizations displaying different metric sets
Two visualizations displaying different metric sets

Suppose we want to calculate what percentage of our revenue each year is spent on marketing campaigns.

Choose Data Visualization in the toolbar and then Formula Visualization to open the formula bar, and enter a formula that refers to a measure from each metric set to combine them.

In our example, we click the Campaign Spend measure line in the chart to insert it into the formula, type / to divide it, then click the Revenue measure line in the other chart to insert it as the divisor.

Formula referring to two different charts
Formula referring to two different charts

Click apply to create the new formula metric set and visualization.

Edit the new formula metric set's measure
Edit the new formula metric set's measure

Since we have calculated a percentage in our example, we will want to click to edit the formula measure in the Data Analysis Panel. We can change the Caption and also set its Format to P1, to format it as a percentage with one decimal place.

New formula visualization, with percent formatting
New formula visualization, with percent formatting

4. Grouping and aggregating

Many formula functions have optional alignment arguments, which can change how the function groups data, and can result in multiple rows or columns of data instead of just one.

To make use of this ability, select a measure with multiple hierarchies. For example, this table lists products grouped by Color:

List price by product, grouped by color
List price by product, grouped by color

While editing a dashboard displaying this metric set, choose Data Visualization in the toolbar and then Formula Visualization to open the formula bar.

After entering a function such as AVG or SUM and the input values, type a comma, then click a hierarchy value shown on your dashboard or type in the hierarchy as the second, alignment argument, before the closing parenthesis:

Including an alignment hierarchy argument
Including an alignment hierarchy argument

In our example, we are calculating the average list price for each color. Click apply to create the new formula metric set and visualization.

Average product list price by color
Average product list price by color

Another helpful function is the TOP or maximum subset function, which we can use with an alignment argument to provide the top (1) product for each color. Other subset functions can be used the same way, such as BOTTOM, FIRST and LAST.

Top product for each color (totals turned off)
Top product for each color (totals turned off)

If totals are included that you don't want, you can disable them the same way as for any metric set: click the metric set's edit button in the Data Analysis Panel, then set Shown Totals.

Editing the metric set totals settings
Editing the metric set totals settings

5. Subset functions

When used in formula visualizations, subset functions can be used to reduce the full set of data to a particular subset of its rows or columns. This isn't possible when just adding a new formula measure column to an existing metric set.

For example, the TOP function that we used above with an alignment argument can also be used to ask for the top list price values:

Top 3 list prices, sorted descending
Top 3 list prices, sorted descending

Because a formula visualization produces a completely new metric set and result, formulas can be used in this way to change the rows or columns that are displayed compared with the original metric set.

Tip
When using formulas to select the top N items, a parameter placeholder could be used with a filter to allow viewers to change how many items are displayed. See Adding formulas for details on parameter placeholders.

6. Filtering the formula metric set

The result of creating a formula visualization is a separate metric set, which is completely independent from the original metric set. This means it's safe to delete the original visualization if you don't need it; otherwise, the two metric sets now work independently, including their sorting and filtering. If you have placed filters on your dashboard, you will need to connect them to the new metric set for it to be affected.

To connect an existing filter on the canvas to the formula visualization, click to select it while editing, then choose Connect Filters in the toolbar. When adding a new filter, the same Filter Visualizations popup appears automatically:

Filtering the formula visualization
Filtering the formula visualization

Check the box next to the hierarchy under the new visualization and its metric set.

Hierarchies that are placed under Slicers in the original metric set can be filtered using the parameter that appears under the formula metric set:

Connecting a filter to the slicer
Connecting a filter to the slicer

Connect your filter to this parameter, or you can add a view parameter in the Parameters window connected to it to change the filter value without adding a filter control.

7. Changing the formula metric set

When a formula visualization is created, its new metric set initially has certain measures or hierarchies based on the particular formula you entered. This determines how the data is grouped, as well as which information can be displayed within the new visualization.

If you want to change which hierarchies and measures are included, you can do so. This does not affect the original metric set, but only how this formula metric set groups and displays its result.

7.1. Removing elements

You can remove any element from the Data Analysis Panel that you don't want (including the measure containing the original formula).

Deleting elements from the formula metric set
Deleting elements from the formula metric set

When a hierarchy is removed, this affects how the result is grouped into rows or columns, and the remaining data is re-aggregated accordingly.

Result after deleting elements
Result after deleting elements

7.2. Adding elements

You can add any elements that are missing from the formula metric set, as long as they were already selected in the original metric set(s).

While you can't use the click to add links, you can drag the same elements from the Explore window that were originally added to the original metric set, and drop them onto the Data Analysis Panel of the new formula metric set. They must be in the same place they were added in the original metric set (e.g., under Rows, if originally added there).

Adding missing elements to the formula metric set
Adding missing elements to the formula metric set

If you need to add an element that is not already present in the original metric set, first add it to the original metric set. You will then be able to drag it onto the formula metric set, even if you created it earlier.

8. Advanced options

When using multiple metric sets in your formula, there are settings available that determine how the data is combined.

To access these settings, click the fx button of a measure in your formula metric set, and click any of the underlined formula functions (e.g., AVG). If you haven't used one of these functions yet, add a new formula measure to the formula metric set that uses one (you can remove it later).

In the formula dialog that appears, there are two options at the bottom:

  • Alignment Behavior determines whether the rows of data in each metric set are matched up by their hierarchy's values (Hierarchical or Auto), or are combined arbitrarily by sequence (Positional).
  • Enforce Hierarchy Cube Check has been available as an option since Dundas BI version 5, and is disabled by default. When un-checked and metric sets select data from different data cubes, hierarchies with the same unique name are assumed to be the same, allowing their rows of data to be matched up. If hierarchies should not be considered the same based only on their names for the data cubes you are using, you can check this option.

9. Notes

Formula visualizations have some limitations compared with the original metric sets:

  • Like other formulas, they can't be created based on a metric set in raw data mode. Remove the <Row Number> element from Rows in the metric set's Data Analysis Panel before creating a formula visualization.
  • Period over period measures can't be added to a formula metric set. They can instead be added to the original metric before creating the formula visualization.
  • Formula metric sets don't support expanding or collapsing individual members/rows.

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