Skip to main content
Last updated on

Level of detail expressions

Level of detail expressions let you specify a granularity at which aggregations are calculated which is different than the level of detail of your visualizations. This page explains how to use level of detail expressions in AI/BI dashboards.

What are level of detail expressions?​

Level of detail expressions allow you to specify exactly which dimensions should be used when calculating an aggregate, regardless of the dimensions present in your visualization. This gives you fine-grained control over the scope of your calculations. Level of detail expressions can be either dimensions or measures.

There are two types of level of detail expressions:

  • Fixed level of detail: Aggregate over pre-defined set of dimensions specified in the expression itself, ignoring all other dimensions in the visualization.
  • Coarser level of detail: Aggregate at a coarser level than the visualization by excluding a specific dimension from the grouping set.

Level of detail expressions are authored using window functions in dataset calculations in specific usage patterns.

When to use level of detail expressions​

Use level of detail expressions when you need to:

  • Calculate percentages of total (for example, each category's share of total sales).
  • Compare individual values to dataset-wide aggregates (for example, sales versus average sales).
  • Create cohort or segment-level metrics that remain constant across different groupings.
  • Perform multi-level aggregations in a single calculation.

Defining expressions at a fixed level of detail​

A fixed level of detail expression computes an aggregate at the granularity that you define, ignoring the dimensions in your visualization. Fixed level of detail expressions are dimensions, and are implemented using scalar window functions with PARTITION BY clauses.

Syntax​

SQL
<AGGREGATE_FUNCTION>(<column>) OVER (PARTITION BY <dimension1>, <dimension2>, ...)

To aggregate over the entire dataset, omit the PARTITION BY clause and leave empty parentheses after OVER.

When to use a fixed level of detail​

Use fixed level of detail expressions when you need:

  • No dependency on visualization groupings: Metrics that have static partitioning across all uses.
  • Dataset-level aggregates: Global aggregates compared with other row levels or groupings (for example, percent of total sales by region).
  • Multi-level hierarchies: Detail-level and rollup-level metrics in the same visualization.

Example: Regional sales totals​

Suppose you have a sales dataset and want to show each product's sales alongside the total sales for its region. Here's sample data:

Region

Product

Sales

West

Laptop

5000

West

Mouse

500

East

Laptop

6000

East

Monitor

3000

To calculate total sales by region, use:

SQL
SUM(Sales) OVER (PARTITION BY Region)

Result:

Region

Product

Sales

Region Total

West

Laptop

5000

5500

West

Mouse

500

5500

East

Laptop

6000

9000

East

Monitor

3000

9000

Each row shows the individual product sales and the fixed total for its region. The region total remains constant regardless of how you filter or group by product.

Example: Percent of total​

Fixed level of detail expressions can be composed into more complex expressions, including calculated measures performing "percentage of total" calculations. To do this, you can reference fixed level of detail expressions within a measure expression to be dynamic to visualization groupings.

For example, we can first define total_sales as a fixed level of detail expression as a standalone calculation operating over the whole dataset:

SQL
SUM(sales) OVER ()

Then we can reference the fixed level of detail expression in a calculated measure definition:

SQL
SUM(Sales) / ANY_VALUE(total_sales)

The above expression can be used as a measure in visualizations that group by any dimension, such as Product, to show the percent of total sales from each distinct Product.

note

A fixed level of detail expression needs to be wrapped in an aggregate function to be used in a measure calculation. If the result of the fixed level of detail expression is expected to be constant and repeated within rows of a group, a function like ANY_VALUE can be used to return a single value from the aggregated results, as seen in the above example.

Filtering on fixed level of detail expressions​

Since fixed level of detail expressions are computed before visualization groupings and filters are applied, if you want them to be affected by dynamic filters on your dashboard, you must define such filters as parameters that affect the underlying SQL dataset text. See Work with dashboard parameters for more details on dashboard parameters.

Defining expressions at a coarser level of detail​

Expressions at a coarser level of detail compute an aggregate at a coarser granularity than the visualization granularity by excluding a dimension from the visualization's grouping set. Such expressions are measures and are implemented using aggregate window functions with the ALL range specifier. Conceptually, aggregating over a window consisting of the full range of a dimension is the same as excluding that dimension from the grouping entirely.

Syntax​

SQL
<AGGREGATE_EXPRESSION> AGGREGATE OVER (ORDER BY <dimension> ALL)

When to use a coarser level of detail​

Use coarser level of detail expressions when you need:

  • Dynamic groupings: Calculate percentages or aggregates that adapt to visualization groupings (for example, percent of total for any selected dimension)
  • Filter-aware aggregations: Calculate at a coarser granularity while respecting dashboard filters

Example: Percent of total​

The most common use case for coarser level of detail expressions is calculating a percent of total value. To calculate a percent of total sales by Region, but within a dynamic partition:

SQL
SUM(Sales) / (SUM(Sales) AGGREGATE OVER (ORDER BY Region ALL))

This expression:

  1. Computes total sales within the partitioning of the visualization; such as Region and Product.
  2. Computes total sales within the partitioning of the visualization, excluding Region, across Product.
  3. Divides the two to get a percentage representing the percentage of each sale that each Region is contributing, for each Product.

Examples​

Product sale frequency​

Calculating the number of sales for each product is straightforward, but calculating the total count of products within specific sales ranges requires a different approach. For example, consider how you would create a histogram that shows counts of products grouped by sales volume buckets.

Custom calculations:

  • Define sale_count as a fixed level of detail expression: COUNT(*) OVER (PARTITION BY Product)

Usage: Create a bar chart grouped by sale_count on the x-axis, and select COUNT(*) on the y-axis.

Compare to cohort average​

How do deal sizes compare to the average deal size in their same region?

Custom calculations:

  • Define average_deal_amount_by_region as a fixed level of detail expression: AVG(deal_amount) OVER (PARTITION BY Region)
  • Define percentage_of_region_average as deal_amount - average_deal_amount_by_region

The above two expressions can also be combined into a single calculation.

Usage: Define a table and select percentage_of_region_average alongside any other dimension needed for the visualization

Limitations​

DISTINCT in scalar window functions​

You can't use DISTINCT directly in scalar window functions. For example, this doesn't work:

SQL
COUNT(DISTINCT customer_id) OVER (PARTITION BY region)

Instead, use ARRAY_SIZE with COLLECT_SET:

SQL
ARRAY_SIZE(COLLECT_SET(customer_id) OVER (PARTITION BY region))

Aggregating scalar window functions​

You can't directly aggregate a scalar window function (used for fixed level of detail) when authoring a measure expression.

Pattern 1: Define the window function as a separate dimension calculation and reference it in the measure expression, as in the percent of total example above.

Pattern 2: Alternatively, for decomposable aggregates like SUM, you can use nested aggregation, which first computes the inner aggregate (not windowed) in each group, and then aggregates across the group results in a second pass:

SQL
SUM(quantity) / SUM(SUM(quantity)) OVER ()

This pattern works for functions like SUM, MIN, MAX, and COUNT, but not for functions like MEDIAN or PERCENTILE which are not decomposable. Note the order of operations carefully as defined by the parentheses; an expression like SUM(SUM(quantity) OVER ()), though similar, will not be recognized as valid and should be expressed using the first pattern in this section.

Additional resources​