Using a formula visualization
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.
In the toolbar, click Data Visualization and select Formula Visualization under Formula.
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.
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).
Type a period character (.) and then from the menu, select the measure from the list of available measures.
Type a closing parenthesis to complete the formula and press Enter or click apply.
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.
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.
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 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.
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 a forward slash ( / ) to divide it, then click the Revenue measure line in the other chart to insert it as the divisor.
Click apply to create the new formula metric set and visualization.
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.
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:
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:
In our example, we are calculating the average list price for each color. Click apply to create the new formula metric set and visualization.
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.
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.
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 3 list price values:
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.
6. Filtering the formula metric set
The result of creating a formula visualization is a separate metric set. This means it's safe to delete the original visualization if you don't need it; otherwise, the two metric sets now request their data 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:
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:
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).
When a hierarchy is removed, this affects how the result is grouped into rows or columns, and the remaining data is re-aggregated accordingly.
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 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).
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.
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
- Video: Formulas
- Adding formulas
- List of formula functions
- Automatic joins and hierarchies
- Metric set analysis tools
- View data with a chart and drill down
- Understanding the Dundas BI Data Model