Using a formula visualization
This walkthrough shows you how to use a Formula Visualization and display the result in a Data Label.
For example, suppose you have a Bar Chart that shows the List Price of each product, and you want to display the Average List Price on a Data Label control.
Related video: Working with Formulas
2. Add the Formula Visualization Control
From the toolbar, click Data Visualization and select Formula Visualization.
In the formula bar, type the first letter of the formula function and select the appropriate entry from the list of available functions.
Type a dollar sign character ($) and select the control from the list the available Data Visualization controls.
Type a period character (.) and select the metric set from the list the available metric sets.
Type a period character (.) and select the measure from the list of available measures.
Type ) after the last dollar ($) sign to complete the formula and click apply.
Regardless of the type of Data Visualization you are using, the result of the Formula Visualization is displayed by default in a tabular format.
2.2. Re-visualize to a Data Label
To display the average in a Data Label control, you can do the following:
- Right-click the Data Visualization and select Re-Visualize.
- Select Data Label.
- Add a Label component with the text Average:.
3. Add a placeholder parameter
You can introduce a new parameter as a placeholder in the formula, which you can then connect to a filter or view parameter. For example, in the above-created data label, open the Data Analysis Panel and edit the formula.
Add an offset placeholder to the formula expression and click apply. The placeholder can be any text but has to be surrounded by dollar ($) signs.
Add a Single Number filter and connect it to the new SingleNumber parameter.
Change the filter label text to Offset.
Switch to View mode and change the filter value, the Data Label will change accordingly.
4. Add an alignment hierarchy
Most formulas and formula visualizations can take an alignment hierarchy, which will group the results by each member in the hierarchy.
To illustrate the behavior, add the Color hierarchy to the bar chart metric set.
Switch to the Visualization tab and remove the Color hierarchy from both the ListPrice series and the Axis.
Add another formula visualization, this time add Color as the alignment hierarchy.
The new formula visualization appears as a table, with a row for every Color member and its average list price.
5. See also
- Video: Working with Formulas
- Connect to data and view it on a dashboard
- Understanding the Dundas BI data model
- List of formula functions
- Adding complex formulas