Calculated values

Using calculated values, you can add measures that are not present in the original dataset to provide a more comprehensive view of the data.

You can create as many calculated values as you need. They can be saved and restored within the report. Each calculated value is described by the MeasureObject.

This feature is available for the "json", "csv", and "api" data source types.

To see different examples of using calculated values, see the Examples page.

Calculated measure properties

See the full list of available MeasureObject properties.

How to add a calculated value

You can add a calculated value in the following ways:

Via UI
In the report
Using API calls

Via UI

Step 1. Open the Field List.

Step 2. Click the Add calculated value button:

We show the location of the Add calculated value button

As a result, the Calculated value pop-up window will appear.

Step 2. In the pop-up window, enter a name for the calculated value and compose a formula: drag the existing values into the formula box and apply arithmetic operators. For example:

Calculated value pop-up window with a defined formula

Note that when using the "json" or "csv" data source types, you will see the Calculate individual values checkbox. If you select it, the formula will be calculated using raw values. Read more in the following section: Use the individual property.

Step 3. Click APPLY to add the calculated value to the Field List. The new value will be added to the Values box and ready to be displayed in the component.

In the report

To add a calculated value in the report, add the MeasureObject to the slice.measures array. The calculated value must have the formula property defined. Check out the example:

slice: {
  rows: [
    // Fields
  ],
  measures: [
    // Other measures
    {
      uniqueName: "Average Quantity",
      formula: "sum('Quantity')/count('Quantity')",
      // Other properties
    }
  ]
}

Live example

Using API calls

You can add a calculated value using the addCalculatedMeasure() API call:

let measure = {
  formula: "sum('Quantity')/count('Quantity')",
  uniqueName: "Average Quantity",
  caption: "Average Quantity",
  grandTotalCaption: "Total Average Quantity",
  active: true
};

pivot.addCalculatedMeasure(measure);

Live example

Use cases

Use multiple measures in a formula

The following example shows how to define a formula with two measures:

slice: {
  rows: [
    // Fields
  ],
  measures: [
    // Other measures
    {
      uniqueName: "Total Price",
      formula: "sum('Price') * sum('Quantity')",
    },
    {
      uniqueName: "Top Category",
      formula: "average('Price') < 4000 and sum('Quantity') > 100",
    },
  ],
}

Live example

Use negative numbers in a formula

To use a negative number in a formula, enclose the number in parentheses. For example:

slice: {
  rows: [
    // Fields
  ],
  measures: [
    // Other measures
    {
      formula: "(-1) * sum('Price')",
      uniqueName: "Inverted Price",
    },
  ],
}

Live example

Use the individual property

When the individual property is set to true, the formula is calculated using raw values.

Note The individual property is available only for the "json" and "csv" data source types.

Let’s see how it works. For example, we have the following data:

[
  {
    "Quantity": 7,
    "Price": 100,
    "Country": "Canada"
  },
  {
    "Quantity": 5,
    "Price": 200,
    "Country": "USA"
  }
]

Based on this data, the formula sum('Quantity') * sum('Price') will be calculated like this:

If the individual is false: (7 + 5) * (100 + 200) = 3600
If the individual is true: 7 * 100 + 5 * 200 = 1700

Here is how to specify the individual property in code:

slice: {
  rows: [
    // Fields
  ],
  measures: [
    // Other measures
    {
      uniqueName: "Revenue (individual)",
      formula: "sum('Quantity') * sum('Price')",
      individual: true
   },
  ],
}

Live example

Specify number formatting for calculated values

You can specify number formatting for a calculated value in the following way:

report: {
  slice: {
    rows: [
      // Fields
    ],
    measures: [
      // Other measures
      {
        uniqueName: "% of Total Price",
        formula: "percent('Price') / 100",
        format: "price",
      },
    ],
  },
  formats: [
    {
      name: "price",
      decimalPlaces: 2,
      isPercent: true,
    },
  ],
}

Live example

Apply conditional formatting to calculated values

You can apply conditional formatting to a calculated value in the following way:

report: {
  slice: {
    rows: [
      // Fields
    ],
    measures: [
      // Other measures
      {
        uniqueName: "Top Category",
        formula: "average('Price') < 4000 and sum('Quantity') > 100",
      },
    ],
  },
  conditions: [
    {
      formula: "#value = 1",
      measure: "Top Category",
      format: {
        backgroundColor: "#66FF99",
      },
    },
  ],
}

Live example

How to remove a calculated value

You can remove a calculated value in the following ways:

Via UI
Using API calls

Via UI

Step 1. Open the Field List.

Step 2. Click the Edit calculated value button near the calculated value name (e.g., Average Quantity). You can find the value in one of the following locations:

In the All fields box.
In the Values box if the value is selected in the slice.

We show the location of the Edit calculated value buttons

Step 3. Click the Remove button. As a result, you will be prompted to confirm the action. Click Yes in the pop-up window.

We show the location of the Remove calculated value button

Using API calls

Use the removeCalculatedMeasure API call to remove a specific calculated measure. For example:

let measureName = "Average Quantity";
pivot.removeCalculatedMeasure(measureName);

Live example

To remove all calculated measures, use the removeAllCalculatedMeasure API call:

pivot.removeAllCalculatedMeasures();

Live example

Available operators and functions for calculated values

Below is a list of all operators and functions that can be used in the formula property. You can also check out a list of available aggregations.

Operator/Function	Description
`+`	Arithmetic addition operator. Syntax: `a + b`.
`-`	Arithmetic subtraction operator. Syntax: `a - b`.
`*`	Arithmetic multiplication operator. Syntax: `a * b`.
`/`	Arithmetic division operator. Syntax: `a / b`.
`^`	Arithmetic power operator. Syntax: `a^2`.
`<`	Comparison less than operator. Syntax: `a < b`.
`<=`	Comparison less than or equal operator. Syntax: `a <= b`.
`>`	Comparison greater than operator. Syntax: `a > b`.
`>=`	Comparison greater than or equal operator. Syntax: `a >= b`.
`==`	Comparison equal operator. Syntax: `a == b`.
`or`	Logical `OR` operator. Syntax: `a or b`.
`and`	Logical `AND` operator. Syntax: `a and b`.
`if`	Conditional operator. If the `else` statement is not specified, cells that do not meet the `condition` will be empty. Syntax: `if(condition, then, else?)` Live example.
`abs`	Function that returns the absolute value of a number. Syntax: `abs(number)`.
`round`	Function that rounds the value to the specified number of decimal places. If the number of decimals is not specified, the value is rounded to the nearest integer. Syntax: `round(number, decimals?)` Live example.
`min`	Function that returns the minimum value. Syntax: `min(number1, number2)`.
`max`	Function that returns the maximum value. Syntax: `max(number1, number2)`.
`isNaN`	Function that checks whether the value is not a number. Syntax: `isNaN(value)`.
`!isNaN`	Function that checks whether the value is a number. Syntax: `!isNaN(value)`.

Changes to Flexmonster Software License Agreement

Calculated values

Calculated measure properties

How to add a calculated value

Via UI

In the report

Using API calls

Use cases

Use multiple measures in a formula

Use negative numbers in a formula

Use the individual property

Specify number formatting for calculated values

Apply conditional formatting to calculated values

How to remove a calculated value

Via UI

Using API calls

Available operators and functions for calculated values

What’s next