---
product: "AG Studio"
title: "Calculations"
description: "Create calculated columns and measures in AG Studio with expressions and functions, and use them in Widgets alongside your other fields."
framework: react
version: "3.1.0"
related:
    - title: "Data Panel"
      url: "https://www.ag-grid.com/studio/analysts/data-panel/"
    - title: "Fields in Widgets"
      url: "https://www.ag-grid.com/studio/analysts/fields-in-widgets/"
    - title: "Formatting Values"
      url: "https://www.ag-grid.com/studio/analysts/formatting-values/"
    - title: "Blanks and Missing Values"
      url: "https://www.ag-grid.com/studio/analysts/blanks-and-missing-values/"
llms: "https://www.ag-grid.com/studio/llms.txt"
---

# Calculations

You can create your own calculations in AG Studio, alongside those your developer provides.

In the example below, a Calculated Column (Total Medals) and a Measure (Total Points) have already been created, just as you would create them yourself.

#### Creating Calculations

```ts
import {
  AgFieldDefinition,
  AgReportState,
  AgStudioApi,
  AgStudioProperties,
  createStudio,
  enableStudioDevValidations,
} from "ag-studio";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableStudioDevValidations();
}

const fields: AgFieldDefinition[] = [
  {
    id: "country",
    format: "textFormat",
  },
  {
    id: "sport",
    format: "textFormat",
  },
  {
    id: "gold",
    format: "integerFormat",
  },
  {
    id: "silver",
    format: "integerFormat",
  },
  {
    id: "bronze",
    format: "integerFormat",
  },
];

const initialState: AgReportState = {
  pages: [
    {
      id: "a",
      widgets: {
        "1": {
          type: "grid",
          dataMapping: {
            cols: [
              { id: "medals.country" },
              { id: "user-created-calculated-column", aggregation: "sum" },
            ],
          },
        },
      },
      widgetLayout: {
        "1": { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 16 },
      },
    },
  ],
  selectedPageId: "a",
  panels: {
    filters: {
      collapsed: true,
    },
  },
  schema: {
    fields: {
      "user-created-calculated-column": {
        name: "Total Medals",
        description:
          "User created calculated column that sums gold, silver, and bronze medals",
        expression: "[medals.gold] + [medals.silver] + [medals.bronze]",
      },
      "user-created-measure": {
        name: "Total Points",
        description:
          "User created measure that measures points as determined by medals",
        expression:
          "3 * SUM([medals.gold]) +\n2 * SUM([medals.silver]) +\nSUM([medals.bronze])",
      },
    },
    expressions: [
      {
        id: "user-created-calculated-column",
        tableId: "medals",
        isMeasure: false,
        format: "integerFormat",
      },
      {
        id: "user-created-measure",
        tableId: "medals",
        isMeasure: true,
        format: "integerFormat",
      },
    ],
  },
};

const studioProperties: AgStudioProperties = {
  mode: "edit",
  initialState,
};

let studioApi: AgStudioApi;

const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
fetch("https://www.ag-grid.com/studio/example-assets/olympic-winners.json")
  .then((response) => response.json())
  .then((data) =>
    studioApi!.setProperty("data", {
      sources: [{ id: "medals", name: "Medals", data, fields }],
    }),
  );
```

[Live example: Creating Calculations](https://www.ag-grid.com/studio/examples/calculations/creating-fields/typescript/)

To add one, open the menu in the title bar of a data source section in the Data Panel and choose:

- **New Calculated Column** for a calculation that returns a value for each row, for example `Sales[Revenue] - Sales[Cost]`.
- **New Measure** for a calculation that returns a single value for the rows it covers, for example `SUM(Sales[Revenue])`.

The new field is added to that data source's section and opens in the Compose Panel, ready for you to edit. Give it a name, write its expression, and choose the format to display its values in. To discard the new field, select Cancel before you first save it.

Choose carefully, because your choice describes what your expression returns. A Calculated Column is evaluated row by row, and you can change its aggregation when you use it in a widget. A Measure is a single value for whatever rows it covers and cannot be aggregated further. AG Studio does not check that your expression matches the type you picked, because the result is only known once the query runs. A mismatch therefore shows up as unexpected values in a widget, not as an error in the panel. For how your developer defines the two, see [Calculated Fields](https://www.ag-grid.com/studio/analysts/data-panel/#calculated-fields).

Fields you create are always fully editable. Unlike fields your developer provides, you can also change their format type and remove them. Select the field in the Data Panel, then select Edit to change it or Delete to remove it.

## Expressions

The Expression box appears only for Calculated Columns and Measures you created yourself. As you type, it offers autocomplete for functions and fields, matches brackets, and highlights syntax errors. Expressions are not case sensitive, so function names, TRUE and FALSE, and the data source and field names in a reference all match whatever case you type.

An expression with an error is still saved, but the field produces no values until you fix the error. A new field with no expression yet also produces no values, so a widget shows it as an empty column.

| Description | Syntax |
| --- | --- |
| Field references | `Medals[Gold]`, `[Total Medals]`, `'Completed Orders'[Date]` |
| Strings | `"string"` (double quotes only) |
| Numbers | `123`, `1.23`, `1e3` |
| Booleans | `TRUE`, `FALSE` |
| Arithmetic operators | `+`, `-`, `*`, `/`, `^` |
| Brackets | `3 * (2 + 1)` |
| Comparison operators | `>`, `>=`, `<`, `<=`, `=`, `==` (alias for `=`), `<>` (not equal) |
| Boolean operators | `NOT x`, `&&`, `\|\|` |
| String concatenation | `a & b` |
| Function calls | `ADD(a, b)` |
| Comments | `-- Single Line`, `// Single Line`, `/* Multi Line */` |

> **Note**
>
> Set the Format to suit the values the expression returns. For example, an expression that returns a number can be shown as Integer or Decimal, but not as Text. If a field's format does not match its values, Widgets that use it may fail to display data.

## Functions

The functions below are grouped by the kind of value they work with.

The example below uses a selection of them in Calculated Columns and Measures over three products, so you can compare each result with the values it was worked out from.

#### Function Results

```ts
import {
  AgReportState,
  AgStudioApi,
  AgStudioProperties,
  createStudio,
  enableStudioDevValidations,
} from "ag-studio";
import { getData } from "./data.ts";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableStudioDevValidations();
}

const initialState: AgReportState = {
  pages: [
    {
      id: "page1",
      widgets: {
        columns: {
          type: "grid",
          dataMapping: {
            cols: [
              { id: "sales.product" },
              { id: "revenue" },
              { id: "net_price" },
              { id: "volume" },
              { id: "group" },
              { id: "code" },
              { id: "label" },
              { id: "days_to_ship" },
              { id: "order_quarter" },
              { id: "in_range" },
            ],
          },
          format: {
            title: {
              enabled: true,
              text: "Calculated Columns",
            },
          },
        },
        measures: {
          type: "grid",
          dataMapping: {
            cols: [
              { id: "group" },
              { id: "total_units" },
              { id: "average_price" },
              { id: "products" },
              { id: "median_units" },
            ],
          },
          format: {
            title: {
              enabled: true,
              text: "Measures by Group",
            },
          },
        },
      },
      widgetLayout: {
        columns: { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 12 },
        measures: { xTrack: 0, yTrack: 12, xSpan: 24, ySpan: 12 },
      },
    },
  ],
  selectedPageId: "page1",
  panels: {
    filters: {
      collapsed: true,
    },
    edit: {
      collapsed: true,
    },
    data: {
      collapsed: true,
    },
  },
};

const studioProperties: AgStudioProperties = {
  mode: "edit",
  initialState,
  data: getData(),
};

let studioApi: AgStudioApi;

// setup Studio after the page has finished loading
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
```

[Live example: Function Results](https://www.ag-grid.com/studio/examples/calculations/function-results/typescript/)

### Aggregation

A Measure returns one value for the rows it covers, so its expression combines those rows with an aggregation function. For example, `SUM(Sales[Revenue])` totals the revenue of every row the Measure covers, and `3 * SUM(Medals[Gold]) + SUM(Medals[Silver])` combines two totals. These are the same aggregations you can choose when you use a field in a widget, described in [Aggregation](https://www.ag-grid.com/studio/analysts/fields-in-widgets/#aggregation).

| Function | Description |
| --- | --- |
| `SUM(field)` | Adds all values together. |
| `AVG(field)` | The mean of all values. |
| `MIN(field)` | The smallest value. |
| `MAX(field)` | The largest value. |
| `COUNT(field)` | The number of values that are not empty. |
| `COUNTD(field)` | The number of distinct values. |
| `FIRST(field)` | The first value, based on the current data order. |
| `LAST(field)` | The last value, based on the current data order. |

### Comparison

These functions compare two values, or test whether a value appears in a list, and return true or false.

| Function | Description |
| --- | --- |
| `EQUALS(a, b)` | Are the two values equal? |
| `NOTEQUAL(a, b)` | Are the two values not equal? |
| `LESSTHAN(a, b)` | Is the first value less than the second? |
| `GREATERTHAN(a, b)` | Is the first value greater than the second? |
| `LESSTHANOREQUAL(a, b)` | Is the first value less than or equal to the second? |
| `GREATERTHANOREQUAL(a, b)` | Is the first value greater than or equal to the second? |
| `IN(value, ...)` | Is the first value one of the values that follow? |
| `BETWEEN(value, low, high)` | Is the value between the two bounds, including the bounds? Numbers and dates only. For example, `BETWEEN(Sales[Discount], 0.1, 0.2)`. |

### Logic

These functions combine or invert true and false values, test whether a value is empty, and choose between values.

| Function | Description |
| --- | --- |
| `IF(test, then, else)` | Returns the second value when the first is true, otherwise the third. |
| `SWITCH(value, match, result, ..., default?)` | The result paired with the first match equal to the value, otherwise the default, or empty if there is no default. For example, `SWITCH(Sales[Region], "UK", "Europe", "Other")`. |
| `AND(a, b)` | Are both values true? |
| `OR(a, b)` | Is either value true? |
| `NOT(value)` | Reverses a true or false value. |
| `ISTRUE(value)` | Is the value true? |
| `ISFALSE(value)` | Is the value false? |
| `ISNULL(value)` | Is the value empty? |
| `ISNOTNULL(value)` | Is the value not empty? |
| `COALESCE(a, b, ...)` | The first of the values that is not empty. For example, `COALESCE(Sales[Discount], 0)` returns 0 for rows with no discount. |

### Maths

These functions perform arithmetic on numbers, including rounding, powers and logarithms.

| Function | Description |
| --- | --- |
| `ADD(a, b)` | Adds two numbers, or joins two pieces of text. |
| `SUBTRACT(a, b)` | Subtracts the second number from the first. |
| `MULTIPLY(a, b)` | Multiplies two numbers. |
| `DIVIDE(a, b)` | Divides the first number by the second. |
| `MODULO(a, b)` | Remainder of the first number divided by the second. |
| `MOD(a, b)` | Remainder of the first number divided by the second. |
| `NEGATE(value)` | Reverses the sign of a number. |
| `ABS(value)` | The value of a number without its sign. |
| `SIGN(value)` | The sign of a number, as -1, 0 or 1. |
| `FLOOR(value)` | Rounds down to the nearest whole number. |
| `CEILING(value)` | Rounds up to the nearest whole number. |
| `ROUND(value, places?)` | Rounds to the nearest whole number, or to the given decimal places. |
| `TRUNCATE(value, places?)` | Drops the decimals, or all but the given decimal places. |
| `POWER(base, exponent)` | Raises the first number to the power of the second. |
| `EXP(value)` | Raises e to the power of a number. |
| `LN(value)` | Natural logarithm of a number. |
| `LOG(base, value)` | Logarithm of the second number to the base of the first. |
| `LOG10(value)` | Base 10 logarithm of a number. |
| `GREATEST(a, b, ...)` | The largest of two or more numbers. |
| `LEAST(a, b, ...)` | The smallest of two or more numbers. |

### Trigonometry

Angles are in radians.

| Function | Description |
| --- | --- |
| `SIN(value)` | Sine of a number. |
| `COS(value)` | Cosine of a number. |
| `TAN(value)` | Tangent of a number. |
| `ASIN(value)` | Inverse sine of a number. |
| `ACOS(value)` | Inverse cosine of a number. |
| `ATAN(value)` | Inverse tangent of a number. |
| `ATAN2(a, b)` | Inverse tangent of the quotient of two numbers. |

### Text

These functions transform text, extract part of it, or match it against a pattern. Matching is case sensitive, so `CONTAINS("Apple", "apple")` is false.

| Function | Description |
| --- | --- |
| `UPPER(text)` | Converts text to upper case. |
| `LOWER(text)` | Converts text to lower case. |
| `SUBSTRING(text, from, length?)` | Part of a piece of text, starting at a position. |
| `POSITION(needle, haystack)` | Position of the first occurrence of one piece of text within another. |
| `LTRIM(text, characters)` | Removes the given characters from the start of a piece of text. |
| `RTRIM(text, characters)` | Removes the given characters from the end of a piece of text. |
| `BTRIM(text, characters)` | Removes the given characters from both ends of a piece of text. |
| `OVERLAY(text, replacement, from, length?)` | Replaces part of a piece of text with another. |
| `LIKE(text, pattern, escape?, matchCase?)` | Wildcard match, where `%` matches any characters and `_` matches one. |
| `NOTLIKE(text, pattern, escape?, matchCase?)` | Is the text not a match for the wildcard pattern? The opposite of `LIKE`. |
| `CONTAINS(text, search)` | Does the text contain the search text? For example, `CONTAINS(Products[Name], "Pro")`. |
| `NOTCONTAINS(text, search)` | Does the text not contain the search text? |
| `STARTSWITH(text, prefix)` | Does the text start with the prefix? For example, `STARTSWITH(Orders[Code], "UK-")`. |
| `ENDSWITH(text, suffix)` | Does the text end with the suffix? |

`LIKE` and `NOTLIKE` match case unless you give `false` as `matchCase`. To ignore case without an escape character, give `""` as the escape, for example `LIKE([Name], "al%", "", false)`.

### Date

The unit is the first argument, given as text, such as `"day"`, `"month"` or `"year"`.

| Function | Description |
| --- | --- |
| `DATEADD(unit, date, amount)` | Adds a number of units to a date. |
| `DATEDIFF(unit, from, to)` | The number of units between two dates. |
| `DATETRUNC(unit, date)` | The start of the period a date falls in. |
| `DATEEND(unit, date)` | The end of the period a date falls in. |
| `DATEEXTRACT(unit, date)` | A single component of a date, such as its year, as a number. |
| `DATEFROMPARTS(year, month, day)` | Builds a date from year, month and day numbers. |
| `CURRENTDATE()` | Today's date. The same value for every row in a query. |
| `CURRENTTIMESTAMP()` | The current date and time. The same value for every row in a query. |

### Statistics

These functions summarise the distribution of a field across the rows it covers.

| Function | Description |
| --- | --- |
| `MEDIAN(field)` | The middle value of the field across the rows covered. With an even number of values, it is the average of the two middle values. |
| `PERCENTILE(field, p)` | The value at percentile `p`, between 0 and 1, of the field. When `p` falls between two values, the result is interpolated, as in Excel's `PERCENTILE.INC`. |
| `PERCENTILES(field, p, ...)` | Several percentiles of the field at once. |