Creating Calculated Dimensions

Create a calculated dimension when you want to concatenate dimensions together, group dimensions, and so on.

You can choose to combine existing dimensions to create your own dimensions.

Note

The number format chosen in Start of the navigation path

Profile Settings Next navigation step

User Preferences End of the navigation path

influences the expected input format for Story Calculated Measures and Calculated Dimensions:

Choosing a number format that uses periods (.) as decimal separators means that commas (,) must be used to separate function parameters (for example, IF(Condition, ValueIfConditionIsTrue, ValueIfConditionIsFalse)).
Choosing a number format that uses commas (,) as decimal separators means that semi-colons (;) must be used to separate function parameters (for example, IF(Condition; ValueIfConditionIsTrue; ValueIfConditionIsFalse)).

If the formula is typed in from scratch, the correct function auto-completion happens based on the user preferences. However, if you copy and paste a full formula string, auto-complete won't be able to adapt if there is a mismatch between separators used and the user preferences.

Create one of the following calculated dimensions to use in your charts and tables:

Concatenated dimension
Grouped dimension

Functions, Conditions, and Operators

The following functions, conditions, and operators are available for creating calculated dimensions. For descriptions of each option, see All Formulas and Calculations.

Functions:

`IF()`	`NOT()`	`ISNULL()`	`LIKE()`	`TOTEXT()`	`TONUMBER()`
`LENGTH()`	`CONCAT()`	`SPLIT()`	`SUBSTRING()`	`TRIM()`	`ENDSWITH()`
`FINDINDEX()`	`REPLACE()`	`LEFT()`	`RIGHT()`	`LOWERCASE()`	`UPPERCASE()`

Note

The ISNULL function identifies NULL values, but won't replace a NULL value with a value.

For example, your calculated dimension has the following formula: CD1=IF(ISNULL(D1), "No Value", D1)

The value in cell D1 will not be changed to show the words “No Value”: it will stay as a NULL value.

Conditions:

AND	OR
> (greater than)	< (less than)
>= (greater than or equal)	<= (less than or equal)
= (equal)	!= (not equal)

Operators:

+ (addition)
- (subtraction)
* (multiplication)
/ (division)

Procedure: Set up a Calculated Dimension

Use the Calculation Editor to set up your calculated dimension.

From a Table: In Builder, under Rows or Columns, select Add Measures/Dimensions Calculated Dimensions Create Calculated Dimension.

From a Chart: In Builder, under Dimensions, select Add Dimension Calculated Dimensions Click to Create a New Calculated Dimension.
In the Calculation Editor under Type, select Calculated Dimension.
Provide a name for your calculated dimension.

Remember

You can press Ctrl + Space in the formula area to display a list of suggestions, or type [ for a list of valid measures and dimensions.

Concatenated Dimension

To create a concatenated dimension, do the following:

In the Edit Formula area, type your formula.

To concatenate two or more dimensions, use a plus (+) between the values. You can add a space as well:

Example
[Product] + " " + [Region]
To close the Calculation Editor, click OK.

The calculated dimension is ready to be used in your chart or table.

Grouped Dimension

You can choose to create your own member groupings for your data. For example, you may want to group sales from specific cities into North America, Europe, and Other regions. Using the Calculation Editor, create your own If, then, else formula.

In the Edit Formula area, type an IF statement with the specific conditions.

Example
IF ([city] = ("Vancouver", "Duluth", "Tijuana"), "North America", IF ([city] = ("Berlin", "Paris"), "Europe", "Other"))
To close the Calculation Editor, click OK.

The calculated dimension is ready to be used in your chart or table.