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.
Building your custom measure
Adding a custom measure
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.
There are three ways to create a measure in the dataview editor.
Click on the plus Icon on the left panel.
Click on the ⁝ icon near a field and click on "Add measure"
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.
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:
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 measures that can be added to measures.
Total - This will enforce calculating the measure, disregarding the chart's dimensions.
For example, Calculating 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 the one side in a one-to-many relationship and the dimension from the many side.
Remove duplicates- this setting ensures a correct result when using certain aggregation types like Sum, Count, and Avg.
To enable a special setting, click the cog icon in the measure dialogue and check the box near the setting you want to enable.