Adding complex formulas

Contents[Hide]

1. Overview

This article will guide you in adding a complex formula. This could be used when having different conditions on a particular measure or data point.

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.

Adventure Works table
Adventure Works table

3. Adding a complex formula

To add a formula, with the visualization selected, click Data Tools on the toolbar and select Add Formula.

Add Formula
Add Formula

On the formula bar, click the advanced button on the right.

Advanced Formula
Advanced Formula

In the expanded formula bar, type your complex formula. In this example, we wrote a formula that will return zero (0) whenever the sales are less than 5M.

Note
You can introduce a placeholder parameter in the formula, which you can then connect to a filter or view parameter. The placeholder can be any text surrounded by dollar ($) signs. For an example of using a placeholder parameter, see the article on Using a formula visualization.

Once you have your conditions set, indicate the Default Caption for the formula measure and click apply.

Apply the complex formula
Apply the complex formula

The new measure can be configured like any other measure. Open the Data Analysis Panel for the data visualization and click Edit.

Edit the formula measure
Edit the formula measure

Change the Format to N1 and indicate a 0M Millions Format.

Edit the formula measure
Edit the formula measure

Result
Result

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