# Adding Formulas

## 1. Overview

In Dundas BI, you apply formulas to data by writing small expressions, much like you would in a spreadsheet program. A formula expression can be a simple constant value, a mathematical expression involving measures, a call to a built-in formula function such as AVG and TOP, or any combination of these.

Although this article demonstrates the various ways of using formulas while editing a dashboard, you can add formula measures when editing a metric set as well.

## 2. Setting Up

To get started, we will use the Adventure Works database to create a table visualization. From the *[Sales].[SalesTerritory]* table, use *Name* as the hierarchy and *SalesYTD* as the measure.

## 3. Accessing the formula bar

Select a data visualization on the dashboard canvas, go to the **toolbar**, click **Data Tools**, and then click **Add Formula**. (When editing a metric set full-screen, simply choose **Add Formula** in the toolbar).

A formula bar appears below the main toolbar. The **output** option lets you choose how you initially want to visualize the new formula measure. There is also an **advanced** mode for entering complex, multi-line formula expressions. Once you are finished entering your formula, click **apply**.

Alternatively, you can also add a formula from the *Data Analysis Panel*: go to the MEASURES section and click the **click to add** link.

Then go to the CALCULATED ELEMENTS section and click <**Formula>**. The formula bar will appear just like before.

## 4. Simple formulas

By default, the formula bar starts off in simple editing mode which allows you to enter a one-line expression without any *return* or *if* statements.

### 4.1. Constant value

The simplest formula expression you can have is a constant number value. As an example, consider a bar chart showing OrderQty as a measure by OrderDate.

Open the formula bar for this chart and click the **output** button. Select the **Line** chart option and then click **Submit**.

Notice that the icon for the output button changes to indicate a Line chart visualization.

Type a constant numeric value into the formula bar and click **apply **(or press the ENTER key). The text box is highlighted in blue and a checkmark appears on the right which indicates the formula syntax is correct.

The formula result is calculated and added to the chart as a Line series.

Open the *Data Analysis Panel* for the chart to see the formula result has been added as a second measure to the underlying metric set (appears with an *fx* icon).

### 4.2. Placeholders

A formula expression can include one or more placeholders representing measure or hierarchy values. To insert a placeholder, type a dollar sign ($) character in the formula bar. You will see a menu that lists available placeholders you can insert.

The inserted placeholder consists of the measure or hierarchy name enclosed between dollar sign characters (for example, *$OrderQty$*). Hierarchy placeholders should be followed by a period (.) and one of the identifiers: *MemberTime*, *MemberNumber*, *UniqueName*, or *Caption*. If no identifier is indicated, *Caption* is used by default.

*$ProductID$.MemberNumber*when the hierarchy has numeric members, and

*if($HierarchyMember$.Caption == "Test"){return 0;}*when the hierarchy has string members.

Another way to insert a placeholder is to click the relevant part of a data visualization. For example, click the data point in a chart to insert a placeholder for the corresponding measure, or click the hierarchy axis to insert a placeholder for the corresponding hierarchy.

### 4.3. Math

You can use arithmetic operators within a formula expression as well as math functions such as Math.Max() and Math.Abs(). These arithmetic operations and math functions are calculated separately in each row.

Examples:

- $OrderQty$ * 0.5
- Math.Abs(-0.75 * $OrderQty$)

### 4.4. Functions

Dundas BI includes over 50 built-in formula functions which you can apply to your data. These functions work with the entire series of values you specify when calculating their result.

Type a letter in the formula bar and you'll see an auto-complete menu showing a list of matching functions you can choose from.

Once you've chosen a function (or typed it in manually along with the open bracket), a tooltip appears which shows the required and optional arguments for the function.

The first argument for the AVG function is a measure placeholder which you can insert by typing a dollar sign character or clicking on a data point.

## 5. Advanced mode

Click the **Advanced** button on the right of the formula bar to go into advanced editing mode. This button works like a toggle allowing you to flip between simple and advanced modes.

In advanced mode, you can enter multiple lines of script including *if* statements, and any other syntax available when using DundasScript. The formula should return a value using the *return* statement, even if it is just a single line.

For example, a constant formula must be entered as:

return 40000;

## 6. Formula visualizations

Go to the dashboard's **toolbar**, click **Data Visualization**, and then click **Formula Visualization** under the FORMULA category.

This opens the formula bar just like for adding a formula measure, except that you are able to optionally refer to data from multiple visualizations or metric sets rather than just one.

When you click **apply**, an entirely new metric set is created and displayed in a new visualization instead of modifying an existing visualization and metric set. This new metric set is called a formula metric set, and it is able to produce an entirely different output than the metric set(s) it was based on, potentially consisting of different rows or columns as well as different measure values.

For a walkthrough on adding formula visualizations, see Using a formula visualization.

## 7. Editing a formula

Once you've added a formula measure, you can go back and edit it by clicking the *fx* button in the *Data Analysis Panel*.

The formula bar opens and the corresponding formula is loaded into the formula bar.

You can make changes to the formula and then click **apply**.