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

Compare each subgroup's average with a benchmark that excludes a selected grouping dimension. GROUPINEX lets you control which dimensions participate in the benchmark, making this pattern useful for comparisons within regions, departments, or product categories.

Example use-case

Compare each gender's average salary with the average for its country. Keep country as a chart grouping dimension, or filter the chart to one country, to use a country benchmark.

Source data: The employees dataset contains one row per employee, with salary , country , and gender columns.

How the calculation works

Formula : AVG(salary) / GROUPINEX(AVG(salary), [], [gender])

  1. Calculate the group average: AVG(salary) averages salaries within the current chart group.

  2. Calculate the benchmark: GROUPINEX(AVG(salary), [], [gender]) excludes gender from grouping. The empty second argument adds no dimensions. Other chart dimensions remain in the benchmark, and filters still apply.

  3. Compare the averages: Divide the group average by its benchmark. A ratio of 1 means the averages match. Enable percentage formatting on the chart to display the ratio as a percentage.

The formula excludes gender rather than fixing the benchmark to country. If you also group by department, the benchmark is the country-department average. To adapt this pattern, replace gender with the dimension your benchmark should ignore.

Example result: With chart percentage formatting, 1.1 displays as 110% , meaning the group's average salary is 10% above its benchmark.

Usage

  • Measure: In a column chart grouped by gender and filtered to one country, shows salary relative to that country's average. Enable percentage formatting on the measure.

  • Having filter: Keep gender groups above the country average using formula > 1 . The threshold uses the raw ratio.

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({<employees_dataset_id>:<salary_column_id>}) / GROUPINEX(AVG({<employees_dataset_id>:<salary_column_id>}), [], [{<employees_dataset_id>:<gender_column_id>}])",
    name: {
      en: "Salary vs country average"
    },
    description: {
      en: "Average salary divided by the benchmark that excludes gender"
    }
  }
);
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 salary divided by the benchmark that excludes gender"
      },
      "ai_context": null,
      "expression": "AVG({<employees_dataset_id>:<salary_column_id>}) / GROUPINEX(AVG({<employees_dataset_id>:<salary_column_id>}), [], [{<employees_dataset_id>:<gender_column_id>}])",
      "format": ",.2af",
      "id": "a4ad615a-5199-4e58-8e0e-73dceba18af0",
      "informat": "numeric",
      "lowestLevel": 0,
      "name": {
        "en": "Salary vs country average"
      },
      "subtype": null,
      "type": "numeric",
      "updated_at": "2024-05-20T13:29:37.273Z",
      "custom_metadata": null,
      "user_id": "34b22f36-4904-497f-8acf-b0ba1d7e83cb"
    }
  ]
}