Measures
Measures are saved aggregations associated with a table. You can use measures to create an official definition of a calculation—like Net Promoter Score—so everyone in your Metabase uses the same formula instead of writing their own versions.
People will see measures as options in the Summarize block of the query builder.
Create measures
If your instance is in read-only remote sync mode, and you enabled sync of the Library, you will not be able to create measures on tables published in the Library while in read-only mode.
Measures are created in Data Studio.
- Go to Data Studio by clicking the grid icon in top right of the screen and selecting Data Studio.
- In Data Studio, go to Tables in the left sidebar, and select the table to define a measure on.
-
In the right sidebar for the table, select Measures.
-
Click +New measure. You’ll see an abridged version of Metabase’s query builder that focuses on aggregation.
-
Add your aggregation formula. For more complex aggregations, check out custom expressions.
For example, you can define a measure like
(CountIf([Score] >= 9) - CountIf([Score] <= 6)) / Count() * 100to calculate Net Promoter Score.
- To preview the results of the measure, click on the three dots icon next to the measure’s name and select Preview.
- Save.
Use measures in the query builder
Measures allow people to use pre-defined aggregations to summarize their data.
To use a measure in the query builder:
- Start a new question from the table that the measure is based on.
- Select the measure in the Summarize block.
The measure’s saved aggregation will be applied behind the scenes. You can use breakouts with a saved measure.
Measures are only available to questions that directly query the measure’s table. Measures aren’t available for questions that join the measure’s table, or that involve nested queries (even if those queries measure’s table as a source themselves).
Delete measures
Deleting a measure won’t break questions using it. The questions that use the deleted measure will “revert” to using the measure’s (now unnamed) aggregation formula.
- Go to Data Studio by clicking the grid icon in top right of the screen and selecting Data Studio.
- In Data Studio, go to Tables, and select the measure’s table.
- In the right sidebar for the table, select Measures.
- Choose the measure you want to delete.
- On the measure’s page, click on the three dots icon next to the measure’s name and select Remove measure.
Explore measures
You can explore measures along dimensions and compare several measures in the Metrics Explorer.
To see all measures on a table, select the table in Data Studio > Managing tables and switch to the Measures tab.
Permissions for measures
To create, edit, or delete measures, people need to:
- Be admins or members of the Data Analysts group. This is needed to access Data Studio.
- Have Create queries permissions on the measure’s table. This is needed to create the measure’s query.
To use a measure, people need to:
- Have Create queries permissions on the measure’s table.
Only measures on published tables are synced to Git
When using Remote Sync:
- Measures on tables that are not published to the Library are not synced;
- Measures on published tables are only synced if Library sync is enabled.
If your instance is in read-only Remote Sync mode, and the Library sync is enabled, you won’t be able to create measures on published tables.
You can, however, sync Metrics on any tables by syncing their collection.
Limitations
- Measures are only available for use in the query builder. For defining reusable bits of SQL, check out Snippets.
- Measures are only available on questions that use the measure’s table as the primary data source.
- All admins and people in the Data Analyst group can create measures on tables they have permissions to query. There aren’t additional controls to restrict who can create measures beyond special groups and data permissions.
- Only measures on published tables are synced to Git.
Further reading
Read docs for other versions of Metabase.