Table calculations

You add table calculations in the Explore view and they appear as green columns in your results table (remember, dimensions are blue, and metrics are orange).

Table calculations are built using raw SQL. That means you can use table calculations to build mathematical, True/False, text, and date-based calculations (basically, anything you can do in SQL, you can do in table calculations).

When to use table calculations

The Lightdash semantic layer allows you to define metrics and dimensions that can be reused across your organization. This creates consistency and saves time by establishing standard definitions everyone can rely on. Sometimes the predefined metrics and dimensions aren’t quite enough, and you might need to perform additional calculations for specific visualizations, create specialized metrics for particular analyses, or build custom logic to better communicate your insights.

How Table Calculations Help

Table calculations work as an additional computational layer that builds on top of:

Semantic layer metrics and dimensions (your standard definitions)
Custom metrics and dimensions (created at the BI layer)
Other table calculations (you can even stack them)

Table calculations are useful for specialized, temporary needs, but if you find yourself repeatedly reusing a table calculation, it’s a sign to add it to dbt so it’s tracked in the semantic layer. This keeps your organization’s metrics centralized and consistently defined. You can read more about adding metrics to your dbt project here.

Creating table calculations

Before you create a table calculation, you need to add some dimensions and/or metrics to your results table. Table calculations work with whatever data you’ve already pulled into your results table - they can reference the dimensions and metrics you’ve selected, as well as other table calculations you’ve built from those same underlying fields. Think of it like building with blocks: you start with your base dimensions and metrics, then you can stack table calculations on top of those, and even build new calculations that reference the ones you’ve already created.

Using quick calculations

Quick calculations are shortcuts to the most common table calculations, these are only available to metrics in the contextual menu in the results table.

To learn more about what these calculations are doing, check out the docs here. Once the table calculation is generated you can edit it to modify the SQL query or update the format.

Using custom table calculations

Once you’ve got some data in your results table, you can create a table calculation by clicking on the + Table calculation in the Results tab of the Explore view:

Write the SQL for your table calculation in the pop-up box

Your table calculation is defined using raw SQL that you write up in this pop up box. If you’re not sure what to write here, you can check out some of our table calculation SQL templates. To reference the metrics and dimensions in your results table, you can either use the autocomplete, or you can manually write the full field name using the format ${table_name.field_name}.

Update the format of your table calculation (if needed)

If needed, you can update the format of your table calculation to things like percent formatting using the format tab.

Format types	Description	Raw value	How it might look like in Lightdash
Default	Default format for the table calculation result	0.75	0.75
Percent	Converts numbers into a percentage	0.75	75%
Currency	Adds currency symbol and default locale format	1234.1234	$ 1234.12
Number	Formats number with prefix and suffix	123.1234	Speed: 123.12 km/h

To delete or edit your table calculation, just click on the gear icon by the field name

If you need to edit or delete a table calculation, you can just click on the toggle beside the field and do what you need to do!

Table calculation execution and limitations

The processing order:

Raw data filtering happens first - Dimension and metric filters are applied to your base data.
All table calculations run - Using this filtered dataset, all calculations are performed.
Table calculation filters apply last - Only then are rows hidden based on calculated values.

Key limitations

Since all calculations are performed before filtering, table calculation filters only hide rows without affecting the underlying calculations. For example: You use table calculations to calculate the percentage_of_shipping_method_amount, then filter to hide rows below 30%. The filtered rows (shown in purple) will disappear but the percentage_total calculation still shows 100% even though the visible percentages in the percentage_of_shipping_method_amount column no longer add up to 100%.