All Collections
Creating Measures in the Data View Editor
Creating Measures in the Data View Editor

No coding is required to write expressions using Astrato's measures in the Data Explorer.

Piers Batchelor avatar
Written by Piers Batchelor
Updated over a week ago

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.

Creating your custom measure

Adding a custom measure

  1. Once you've added and selected the data you want to use in the Data View Editor (DVE), click Define to set up your dimensions, measures, and joins.

  2. There are three ways to create a measure in the dataview 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

  1. Select an operator and a field

  2. Add additional aggregations by clicking on the math operators button

  3. Add brackets as needed to define the calculation order for the measure.

  4. You can add filters and settings for each aggregation by clicking on the filter and cog icon in the box.

  5. Select the format for the measure.

  6. 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.

  1. Select a field

  2. Select the mathematical operator you want to use

  3. Select another field

  4. Create a bracket around the two field boxes

  5. Click on the blue ⨍x icon and select an aggregation type

  6. Optional- Add filter and measure settings.

  7. Select a number format for the measure.

  8. 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.

    • 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.

To add a filter to a measure:

  1. Click on the filter icon

  2. Select a field filter type, and value

  3. Optional - Add more filters

  4. Optional- Select the operator between any two filters

  5. Optional- Use brackets to group filters.

Measure Special Settings

There are special measures that can be added to measures.

  • Total - This will enforce calculating the measure, disregarding the chart's dimensions.
    For example, Calculate the share of sales.

  • Allow Fanout -
    A "fanout" happens when you join two tables, and a single value gets duplicated in the end 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.

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.

  • Known Limitations

    • A measure with parameters is not supported in a conditional measure.

    • Creation and editing of conditional measures can only be done in the Semantic view.

Did this answer your question?