Calculated Column and Pivot

Calculated Column and Calculated Pivot let you create custom fields with dynamic formulas, adding flexibility to your views. Calculated columns display values that are not available as standard fields in the data model. Calculated pivots create calculations based on pivoted values to support comparisons and analysis across categories or time periods.

Once created, a calculated column or pivot is available in any view within the same data model, as long as the user has access to the data model and its referenced fields.

When to use Calculated Columns

• Creating custom metrics: Calculate values such as profit margins, growth rates, or performance scores that are not available as standard fields.
• Combining or manipulating data: Display results derived from other columns. For example, create a Total Price column by multiplying Quantity by Unit Price.

When to use Calculated Pivots

• Creating custom metrics from pivoted data: Define calculations based on the pivoted values for deeper analysis, such as calculating year-over-year growth, measuring variances between categories, or computing percentages across pivoted fields.

Create a calculated column or privot

1. In the Toolbar panel, select the Calculation icon. The submenu expands.
2. Select Calculated Column or Calculated Pivot.
3. Click Add. The Create User Calculation Field dialog opens.
4. Enter a column group heading for your custom column.
5. Set the data type and format.
6. Build your formula using the available fields and operators.
7. Select Validate to check your formula.
8. Click Save to create the calculated column or pivot.

Calculated Columns and Pivots settings

Details settings

Setting	Description
Hide Header	Hide the column header. If enabled and the calculation is empty (necResult=""), the column acts as a spacer.
Column Group Heading	Enter the group title for your custom column.
Column Heading	Enter the column header text that appears in the view.

Format settings

Use format settings to apply the appropriate style—such as rich text, currency, or date—for each data type. Formatting enhances readability, prevents confusion, supports better data entry, and ensures your reports are clear and consistent.

• Data Type: Specifies the kind of value in the field.
• Category: Determines the data display style, with options depending on the data type.
• Options: Additional controls that refine your chosen category.

Data Type	Category	Option
Char	Text fields used for names, codes, or descriptions. None – Displays plain text. Text-Rtf – Supports rich text formatting, including bold, italic, and lists.	N/A
Numeric	Decimal fields used for quantities, financial values, or measurements. None – No specific formatting. Number – Displays as a general numeric value. Currency – Displays as a currency with a symbol. Percentage – Displays as a percent value, multiplied by 100.	Symbol Decimal Places Zeros Leading Negative Format
Integer	Whole number fields used for counts or identifiers. None – No formatting applied. Number – Displays as a general numeric value. Currency – Displays as a currency with a symbol. Percentage – Displays as a percent value, multiplied by 100.	Symbol Decimal Places Zeros Leading Negative Format
Date	Calendar date fields. None – Displays plain text. Date&Time – Formats as a date, time, or both.	Date Time Century
Time	Time fields representing hours, minutes, or seconds. None – Displays plain text. Date&Time – Formats as a date, time, or both.	Date Time Century
DateTime	Fields that store combined date and time values. None – Displays plain text. Date&Time – Formats as a date, time, or both.	Date Time Century

Manage Scripts settings

Use the Expression Builder Wizard to create a JavaScript formula for your calculated column. The wizard shows all available fields in the data model, along with functions, statements, operators, and constants.

Select a field name or function to insert it in the Script area. Your script must start with necResult= and end with a semicolon (;).

Setting	Description
Script Area	Provides an editable space where you build your calculation script.
Object Selector	Displays fields, functions, and operators available for your calculation, and includes a search to filter the list.
Description Area	Shows details about the selected item, when available.

note

Use global variables in calculated pivots to make calculations dynamic. For example, to compare margins year over year:

Static calculation: (2019-Margin)/(2019-Margin)-(2018-Margin)
Dynamic calculation: (@@THISYEAR-Margin)/( @@THISYEAR -Margin)-( @@LASTYEAR-Margin)

Add a caculated column or pivots to a view

1. In the Toolbar panel, select the Calculation icon. The submenu expands.
2. Select Calculated Column or Calculated Pivot.
3. Choose and expand the column or pivot you want to add.
4. Click Add to View Columns.

Edit a caculated column or pivot

1. In the Toolbar panel, select the Calculation icon. The submenu expands.
2. Select Calculated Column or Calculated Pivot.
3. Choose and expand the column or pivot you want to edit.
4. Make your changes needed in the Details, Format, and Manage Scripts sections.
5. Click Add to View Columns.

Delete a caculated column or pivot

1. In the Toolbar panel, select the Calculation icon. The submenu expands.
2. Select Calculated Column or Calculated Pivot.
3. Choose and expand the column or pivot you want to remove.
4. Click Delete.