Introducing Custom Measures

Using Astrato's Custom Measures, create the metrics that suit your business using orders of operation with multiple fields, aggregation, and filters- no coding required.

There's also no new syntax or structures to learn. It's a simple point-and-click to create custom measures.

Custom measures, also known as calculations, advanced measures, or multi-field measures, add flexibility to your model.

You can include your custom measure in any workbook. Create your custom measure using the functions, fields, and filters, and save it as a new measure with a description.

Please note: Custom measures are not supported with Google Sheets.

Introducing Custom Measures
Creating your custom measure
Creating a Conditional Measure

Creating your custom measure

Adding a custom measure

Once you've added and selected the data you want to use in the semantic layer editor, click Define to set up your dimensions, measures, and joins.
There are three ways to create a measure in the semantic layer editor.
1. Click on the plus Icon on the left panel.
2. Click on the ⁝ icon near a field and click on "Add measure"
3. In the Data Explorer, you can check the box in any measure header and click on the measure button in the top right corner.

Creating a Custom Measure

Select an operator and a field
Add additional aggregations by clicking on the math operators button
Add brackets as needed to define the calculation order for the measure.
You can add filters and settings for each aggregation by clicking on the filter and cog icon in the box.
Select the format for the measure.
Click Create. You can now use the measure with the data in your workbook.

Creating a Row-Level Measure

A Row-Level measure is a unique use case when the mathematical operation is done first and then the aggregations.
For example, calculating total value when you only have quantity and unit price.

Below are the steps to create a Row-Level measure.

Select a field
Select the mathematical operator you want to use
Select another field
Create a bracket around the two field boxes
Click on the blue ⨍x icon and select an aggregation type
Optional- Add filter and measure settings.
Select a number format for the measure.
Click Create. You can now use the measure with the data in your workbook.

Adding Filters to a Measure

Measure filters allow users to extend the range of business questions they can answer by creating a wider range of measures.
Measure filters are applied only to the measures and not to the tables.
Each type of field has different filter options.
There are two types of filters:
- Value - comparing the selected field to a static value defined manually.
  To learn more about using special characters in the search string, check out this article.
- Field - comparing the selected fields to another field.
A unique type of filter is offered for Date\Timestamp fields predefined periods. This type of filter is a dynamic date range applied as a filter on the selected date field.
More on predefined filters.
There is no limit on the number of filters added to a measure; a user can set the operator between two filters and use brackets to group the filters in the desired order.
Only fields from joined tables and "reachable" tables can be used as filters.
Fields that are not joined or reachable meaning they might create duplicate records in the results, are grayed out, and can't be selected.
Parameters can be used in filters to support dynamic filters that users will be able to change on the fly.
Check this article to learn more about parameters and how to use them.

To add a filter to a measure:

Click on the filter icon
Select a field filter type, and value
Optional - Add more filters
Optional- Select the operator between any two filters
Optional- Use brackets to group filters.

Measure Special Settings

There are special settings that can be applied to measures.

Total - This will enforce calculating the measure, disregarding the chart's dimensions.
For example, calculate the percentage of sales.
Abs- This will return the absolute value (always a positive value)
Allow Fanout -
A "fanout" occurs when you join two tables, resulting in a single value being duplicated in the final result. This screws up aggregation since some of the values are being double-counted.
This feature allows you to calculate measures from one side in a one-to-many relationship and the dimensions from the many sides.
- Remove duplicates- this setting ensures a correct result when using certain aggregation types like Sum, Count, and Average.

Use null in a measure filter- this setting is relevant when the aggregation used in the measure is Sum.
The use cases for using this setting are when the measure is used in advanced chart calculations.

When the setting is enabled, the measure SQL code will appear as follows.

SUM(
    CASE WHEN -- FILTER
      "cal_order_date_New" BETWEEN TO_DATE('2025-01-01','YYYY-MM-DD') AND TO_DATE('2025-04-29','YYYY-MM-DD')
    THEN "Sales_update_sales"
    ELSE NULL
    END
  ) AS m1

When the setting is disabled, the measure SQL code will appear as follows.

SUM(
    CASE WHEN -- FILTER
      "cal_order_date_New" BETWEEN TO_DATE('2025-01-01','YYYY-MM-DD') AND TO_DATE('2025-04-29','YYYY-MM-DD')
    THEN "Sales_update_sales"
    ELSE 0
    END
  ) AS m1

To enable a special setting, click the cog icon in the measure dialogue and check the box near the setting you want to enable.

Creating a Conditional Measure

Conditional Measure Introduction
The conditional measure is used when there is a need to compare to aggregated values, and the comparison can't be done now at the lower granularity level.
For example, when targets and actuals are stored at the product level, there is a need to analyze the performance at a category level.

Creating a New Conditional Measure
- Click on "add measure" in the data modeling screen in the Semantic View and select conditional measure.
- In the modal that opens, create your conditional measures.
  A conditional measure needs four inputs:
  - A measure - you can select from existing measures or create a new measure.
    *Please note that if you select an existing measure, the measure definitions are copied to the new measure.
  - A value to compare can be either a numeric value or another measure.
  - Value to show if the condition is met. It can be either a numeric value or a measure.
  - Value to show if the condition is not met. It can be either a numeric value or a measure.
- Give the measure a name and save.
Editing a New Conditional Measure
- click on the edit option from the three-dot menu of the measure.
- In the modal, the opens edits the measure and saves.
- To edit one of the measures in the condition, click on the pen icon.
  The measure definitions will appear in a modal on the right side.