# Formula

Understanding Formulas in RestBI

A `Formula `

represents a calculated field within your BI model. Formulas allow you to create derived data by applying calculations, aggregations, or conditional logic to the columns in your model. Formulas can reference existing columns or other formulas, and they can be as simple or as complex as needed. The system also supports custom functions, which can be extended to add new capabilities.

**Example**

**Example**

Here is an example of how a `Formula`

might be defined:

**Formula Reference**

**Formula Reference**

### How an Expression is Generated

The **expression** in a formula is a critical component that determines the calculation or logic applied to your data. It’s essentially a pass-through SQL expression that gets directly translated into the SQL query sent to your database. Understanding how this works will help you craft powerful and efficient formulas.

**What is a Pass-Through SQL Expression?**

A pass-through SQL expression means that whatever you write in the formula’s `expression`

field will be directly incorporated into the SQL query generated by the system. This allows you to leverage the full power of SQL, including functions, operators, and conditional logic, without any additional abstraction or translation layer.

**Components of an Expression**

**Components of an Expression**

**Column References**

You can reference any column in your model using the `{{Column Name}}`

syntax.

When the formula is processed, `{{Column Name}}`

is replaced by the fully qualified column name in the resulting SQL query, including any table aliases.

This expression would be converted to something like:

**Other Formulas**

Formulas can reference other formulas using the `{{Formula Name}}`

syntax.

The system will recursively resolve these references, replacing them with the corresponding SQL expressions.

This expression will expand to include the full logic of the `Formula Bikes`

formula:

**SQL Functions**

You can directly use SQL functions like `SUM`

, `AVG`

, `COUNT`

, `CASE WHEN`

, `EXTRACT`

, and any other function supported by your database.

This will be passed directly to the SQL query as:

**Custom Functions**

You can define and use custom functions that extend SQL functionality. These are useful for complex operations that are not natively supported by SQL.

If `PCT_OF_TOTAL`

is a custom function, it will be translated into its SQL equivalent, such as:

**Pre-Built Filter Keys**

Pre-built keys like `THIS_YEAR`

or `LAST_MONTH`

can be used in your expressions, and they are expanded into SQL at runtime.

This would be expanded to:

**See Also**

**See Also**

**Columns**- Discover how Formulas interact with Columns to create dynamic data points.**Tables****-**Learn how Tables house the Columns that Formulas reference.**Joins****-**Understand how to combine data from multiple Tables, which can be used in Formulas.**Query Filters****-**Explore how to apply conditions to the results of Formulas using Query Filters.

Last updated