Adding complex formulas
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.
3. Adding a complex formula
To add a formula, with the visualization selected, click Data Tools on the toolbar and select Add Formula.
On the formula bar, click the advanced button on the right.
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.
Once you have your conditions set, indicate the Default Caption for the formula measure and click apply.
The new measure can be configured like any other measure. Open the Data Analysis Panel for the data visualization and click Edit.
Change the Format to N1 and indicate a 0M Millions Format.