Creating formulas

Dimension member formulas are configured in account dimensions to perform on-the-fly calculations such as gross margin % or labor as a percent of total operating cost. Here are some other key features of dimension member formulas:

• You can use dimension member formulas to calculate account values that are restricted to other dimension member values such as prior year revenue.
• A dimension formula is available any story that uses a model that contains the account dimension with a formula.
• An account dimension formula can be used as-is (from the dimension) or they can be modified in specific models. In this scenario, the model can contain its own version of the dimension formula.
• You can also use dimension member formulas to calculate account values that are restricted to other dimension member values such as prior year revenue.
• Dimension member formulas can be viewed in stories by displayed the formula bar.

Create a dimension member formula:

1. Add a formula into the account dimension
2. View it in a story

You can add measures to the model so that you don't have to create them in stories. These are calculated on the fly (the results aren't stored. Because they aren't stored, calculated measures do not appear in the data foundation view.

1. Go to the calculated workspace
2. Add the new measure
3. Create the formula
4. Preview the results

Calculated measure aggregation settings prevent unwanted summation, for example, the default aggregation (sum) is fine for a calculated measure like: Revenue = Price x Quantity.

In the example below, however, the calculated measure is: Price = Revenue / Quantity, and the summation is not valid. To avoid summation, the exception aggregation type is set to None and the exception aggregation dimensions is set to Date and store.