Hi svetla87,

Good instinct to push the full KPI universe into the semantic layer. You don't need window functions for composite metrics like a utilization ratio. Metric views support composability: a measure can reference other measures with the MEASURE() function, so you can define your atomic measures once and then build ratios, conditional percentages, and growth rates on top of them. That keeps the whole KPI set living in the metric view as proper measures rather than as workarounds.

Here is the shape of it, where utilization_ratio is defined purely in terms of two other measures:

version: 1.1
measures:
  - name: total_credit_limit
    expr: SUM(credit_limit)

  - name: total_balance
    expr: SUM(balance)

  - name: utilization_ratio
    expr: MEASURE(total_balance) / MEASURE(total_credit_limit)
    format:
      type: percentage

A few things worth knowing as you build out the rest of the set. A measure can reference base columns, earlier-defined fields, or earlier-defined measures, so order your definitions so each composite metric comes after the measures it depends on. For genuinely time-based metrics (trailing averages, period-over-period, cumulative totals) you would reach for window measures instead, via the window: block with order, range, and offset. But for averages and ratios composed from other measures, plain composability is the recommended path and avoids the self-join cost that window measures carry.

Sources:

• Advanced techniques for metric views (composability, ratios, window measures)
• Model metric views (measure syntax and referencing other measures)
• Unity Catalog metric views (overview)