Format

Overview

The format property hints to BI tools how to display measure values. This affects rendering in dashboards and reports.

Example

measures:
  - name: total_revenue
    type: sum
    sql: amount
    format: currency

  - name: conversion_rate
    type: number
    sql: "\{conversions\} / NULLIF(\{visits\}, 0)"
    format: percent

  - name: order_count
    type: count
    # No format - displays as plain number

Supported Formats

percent

Displays value as a percentage:

- name: completion_rate
  type: number
  sql: "\{completed\} / NULLIF(\{total\}, 0)"
  format: percent

Output: 75% instead of 0.75

currency

Displays value as monetary amount:

- name: total_revenue
  type: sum
  sql: amount
  format: currency

Output: $1,234.56 (formatting depends on BI tool locale)

Usage Notes

Format vs Calculation

Format is for display only—it doesn't change the underlying value:

# Value is 0.75, displayed as 75%
- name: rate
  type: number
  sql: "\{part\} / NULLIF(\{whole\}, 0)"
  format: percent

# If you want the value to be 75, multiply in SQL
- name: rate_as_whole_number
  type: number
  sql: "100.0 * \{part\} / NULLIF(\{whole\}, 0)"
  format: percent  # Displays as 7500% - probably not what you want!

Percentages

For percentages, your SQL should return a decimal (0.75 for 75%):

# Correct - returns 0.75, displays as 75%
- name: conversion_rate
  type: number
  sql: "\{conversions\} / NULLIF(\{visits\}, 0)"
  format: percent

# Also correct - explicit multiplication for clarity
- name: conversion_rate
  type: number
  sql: "1.0 * \{conversions\} / NULLIF(\{visits\}, 0)"
  format: percent

Currency

For currency, return the raw numeric value:

# Correct - returns 1234.56, displays as $1,234.56
- name: revenue
  type: sum
  sql: amount
  format: currency

Common Patterns

Financial Measures

measures:
  - name: revenue
    type: sum
    sql: amount
    format: currency

  - name: cost
    type: sum
    sql: cost
    format: currency

  - name: profit_margin
    type: number
    sql: "(\{revenue\} - \{cost\}) / NULLIF(\{revenue\}, 0)"
    format: percent

Conversion Metrics

measures:
  - name: visits
    type: count_distinct
    sql: session_id

  - name: signups
    type: count

  - name: signup_rate
    type: number
    sql: "\{signups\} / NULLIF(\{visits\}, 0)"
    format: percent

BI Tool Support

Format support varies by visualization tool. Most tools recognize percent and currency, but rendering specifics (decimal places, currency symbol) depend on the tool's configuration.