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:

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:

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:

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

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

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.

What’s next

• Calculated values examples
• How to define a format for date and time
• How to manage Flexmonster functionality
• How to customize the Toolbar