# Metrics

A metric is a value that describes or summarizes features from a collection of data points.
For example, `Num unique user ids`

is a metric. It describes the unique number of `user_id`

s in a collection of `user_id`

data points.

In Lightdash, metrics are used to summarize dimensions or, sometimes, other metrics.

## #

Adding metrics to your projectTo add a metric to Lightdash, you define it in your dbt project under the dimension name you're trying to describe/summarize.

## #

Metric CategoriesEach metric type falls into one of these categories. The metric categories tell you whether the metric type is an aggregation and what type of fields the metric can reference:

### #

Aggregate metrics:Aggregate metric types perform (surprise, surprise) aggregations. Sums and averages are examples of aggregate metrics: they are measurements summarizing a collection of data points.

Aggregate metrics can *only* reference dimensions, not other metrics.

### #

Non-aggregate metrics:Non-aggregate metrics are metric types that, you guessed it, do *not* perform aggregations.

Numbers and booleans are examples of non-aggregate metrics. These metric types perform a calculation on a single data point, so they can only reference aggregate metrics. They *cannot* reference dimensions.

## #

Metric types:Type | Category | Description |
---|---|---|

average | Aggregate | Generates an average (mean) of values within a column |

boolean | Non-aggregate | For fields that will show if something is true or false |

count | Aggregate | Counts the total number of values in the dimension |

count_distinct | Aggregate | Counts the total unique number of values in the dimension |

date | Non-aggregate | For measures that contain dates |

max | Aggregate | Generates the maximum value within a column |

min | Aggregate | Generates the minimum value within a column |

number | Non-aggregate | For measures that contain numbers |

string | Non-aggregate | For measures that contain letters or special characters |

sum | Aggregate | Generates a sum of values within a column |

### #

averageTakes the average (mean) of the values in the given field. Like SQL's `AVG`

function.

The `average`

metric can be used on any numeric dimension or, for custom SQL, any valid SQL expression that gives a numeric table column.

For example, this creates a metric `avg_price`

by taking the average of the `item_price`

dimension:

### #

booleanTells you whether something is True or False.

The `boolean`

metric can be used on any valid SQL expression that gives you a `TRUE`

or `FALSE`

value. It can only be used on aggregations, which means either aggregate metrics *or* custom SQL that references other metrics. You cannot build a `boolean`

metric by referencing other unaggregated dimensions from your model.

`boolean`

metrics don't do any aggregations; they just reference other aggregations.

For example, the `avg_price`

metric below is an average of all of the `item_price`

values in our product table. A second metric called `is_avg_price_above_20`

is a `boolean`

type metric. The `is_avg_price_above_20`

metric has a custom SQL expression that tells us whether the `avg_price`

value is greater than 20.

### #

countDoes a table count, like SQLâ€™s `COUNT`

function.

The `count`

metric can be used on any dimension or, for custom SQL, any valid SQL expression that gives a set of values.

For example, this creates a metric `number_of_users`

by counting the number of `user_id`

values in the table:

### #

count_distinctCounts the number of distinct values in a given field. It's like SQLâ€™s `COUNT DISTINCT`

function.

The `count_distinct`

metric can be used on any dimension or, for custom SQL, any valid SQL expression that gives a set of values.

For example, this creates a metric `number_of_unique_users`

by counting the number of unique `user_id`

values in the table:

### #

dateGives you a date value from an expression.

The `date`

metric can be used on any valid SQL expression that gives you a date value. It can only be used on aggregations, which means either aggregate metrics *or* custom SQL that references other metrics. You cannot build a `date`

metric by referencing other unaggregated dimensions from your model.

To be honest, `date`

metrics are pretty rarely used because most SQL aggregate functions don't return dates. The only common use of this metric is if you use a `MIN`

or `MAX`

on a date dimension.

### #

maxMax gives you the largest value in a given field. It's like SQLâ€™s `MAX`

function.

The `max`

metric can be used on any dimension or, for custom SQL, any valid SQL expression that gives a set of values.

For example, this creates a metric `max_delivery_cost`

by looking at the `delivery_cost`

dimension and taking the largest value it finds:

### #

minMin gives you the smallest value in a given field. It's like SQLâ€™s `MIN`

function.

The `min`

metric can be used on any dimension or, for custom SQL, any valid SQL expression that gives a set of values.

For example, this creates a metric `min_delivery_cost`

by looking at the `delivery_cost`

dimension and taking the smallest value it finds:

### #

numberUsed with numbers or integers. A `number`

metric doesn't perform any aggregation but can be used to perform simple transformations on other metrics.

The `number`

metric can be used on any valid SQL expression that gives you a numeric or integer value. It can only be used on aggregations, which means either aggregate metrics *or* custom SQL that references other metrics. You cannot build a `number`

metric by referencing other unaggregated dimensions from your model.

For example, this creates a metric called `total_gross_profit_margin_percentage`

based on the `total_sale_price`

and `total_gross_profit_margin`

aggregate metrics:

The example above also uses the NULLIF() SQL function to avoid division-by-zero errors.

### #

sumAdds up the values in a given field. Like SQLâ€™s `SUM`

function.

The `sum`

metric can be used on any numeric dimension or, for custom SQL, any valid SQL expression that gives a numeric table column.

For example, this creates a metric `total_revenue`

by adding up the values in the `revenue`

dimension:

### #

stringUsed with fields that include letters or special characters.

The `string`

metric can be used on any valid SQL expression that gives you a string value. It can only be used on aggregations, which means either aggregate metrics *or* custom SQL that references other metrics. You cannot build a `string`

metric by referencing other unaggregated dimensions from your model.

`string`

metrics are rarely used because most SQL aggregate functions don't return strings. One common exception is MySQLâ€™s `GROUP_CONCAT`

function.

For example, this creates a metric `product_name_group`

by combining the unique values of a dimension called `product_name`

:

## #

Adding your own metric descriptionsWe add default descriptions to all of the metrics you include in your model. But, you can override these using the description parameter when you define your metric.

## #

Using custom SQL in aggregate metricsYou can include custom SQL in your metric definition to build more advanced metrics using the sql parameter.
Inside the sql parameter, you can reference any other dimension from the given model and any joined models. You **canâ€™t reference other metrics.**

You can reference dimensions from the same model like this: `sql: "${dimension_in_this_model}"`

Or from joined models like this: `sql: "${other_model.dimension_in_other_model}"`

## #

Using custom SQL in non-aggregate metricsIn non-aggregate metrics, you can reference any other metric from the given model and any joined models. You **canâ€™t reference other dimensions.**

You can reference metrics from the same model like this: `sql: "${metric_in_this_model}"`

Or from joined models like this: `sql: "${other_model.metric_in_other_model}"`