Create Formula

LLM-friendly URL
POST
https://api.luzmo.com/0.1.0/formula
API call form
Examples
List of examples
  • Calculate a ratio of distinct counts with COUNTD
  • Calculate a score from conditional counts with IF, COUNT, and COUNTR
  • Compare group averages with the overall average using GROUPFULL
  • Compare averages with a broader group using GROUPINEX
  • Average per-entity totals with nested SUM, GROUPINEX, and AVG
Description

Calculate a total for each entity, then average those totals to measure the average contribution per entity. This pattern is useful for metrics such as average revenue per customer or average volume per location, where the desired average is over entity totals rather than individual rows.

Example use-case

Compare average store revenue across regions by totaling revenue for each store, then averaging those totals. Each store with sales in the selected data contributes equally to the average.

Source data: The sales dataset contains one row per sale, with revenue , store_id , and region columns. store_id uniquely identifies a store. Each store can have multiple sales rows.

How the calculation works

Formula : AVG(GROUPINEX(SUM(revenue), [store_id], []))

  1. Total revenue: The inner SUM(revenue) adds revenue across sales rows.

  2. Calculate one total per store: GROUPINEX(..., [store_id], []) adds store to the chart's grouping dimensions. The empty third argument excludes no dimensions.

  3. Average the totals: The outer AVG(...) averages those totals within each chart group. Each store represented in the selected data contributes one total, regardless of its number of sales rows.

Example result: Store totals of 200 and 600 give average store revenue of 400 , even if the stores have different numbers of sales rows.

Usage

  • Measure: In a column chart grouped by region, shows average revenue for stores with sales in each region.

  • Having filter: Keep regions with average store revenue above 400 using formula > 400 .

Install
npm install @luzmo/nodejs-sdk
Example Request
Shell Node Java .NET Python PHP 
import Luzmo from '@luzmo/nodejs-sdk';
const client = new Luzmo({
  api_key: '<your Luzmo API key>',
  api_token: '<your Luzmo API token>',
  host: 'https://api.luzmo.com'
});


const response = await client.create('formula',
  {
    expression: "AVG(GROUPINEX(SUM({<sales_dataset_id>:<revenue_column_id>}), [{<sales_dataset_id>:<store_id_column_id>}], []))",
    name: {
      en: "Average store revenue"
    },
    description: {
      en: "Average revenue total across stores represented in the selected data"
    }
  }
);
Open in API Form
Example Response
200
400
500
{
  "count": 1,
  "rows": [
    {
      "created_at": "2024-05-20T12:44:23.163Z",
      "currency_id": null,
      "description": {
        "en": "Average revenue total across stores represented in the selected data"
      },
      "ai_context": null,
      "expression": "AVG(GROUPINEX(SUM({<sales_dataset_id>:<revenue_column_id>}), [{<sales_dataset_id>:<store_id_column_id>}], []))",
      "format": ",.2af",
      "id": "a5ad615a-5199-4e58-8e0e-73dceba18af0",
      "informat": "numeric",
      "lowestLevel": 0,
      "name": {
        "en": "Average store revenue"
      },
      "subtype": null,
      "type": "numeric",
      "updated_at": "2024-05-20T13:29:37.273Z",
      "custom_metadata": null,
      "user_id": "34b22f36-4904-497f-8acf-b0ba1d7e83cb"
    }
  ]
}