version: 1.1
source: sales.fact.orders
joins:
  - name: product
    source: sales.dim.product
    on: source.product_id = product.product_id
    cardinality: many_to_one
  - name: customer
    source: sales.dim.customer
    on: source.customer_id = customer.customer_id
    cardinality: many_to_one
  - name: date
    source: sales.dim.date
    on: source.order_date = date.date_key
    cardinality: many_to_one
fields:
  - name: product_name
    expr: product.product_name
  - name: product_category
    expr: product.category
    display_name: 'Product Category'
  - name: customer_segment
    expr: customer.segment
  - name: order_date
    expr: date.full_date
  - name: order_year
    expr: date.year
  - name: order_month
    expr: date.month_name
measures:
  - name: total_revenue
    expr: SUM(revenue)
    display_name: 'Total Revenue'
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
  - name: gross_margin
    expr: SUM(revenue) - SUM(cost)
  - name: order_count
    expr: COUNT(*)
  - name: avg_order_value
    expr: AVG(revenue)
  - name: revenue_to_budget
    expr: (SUM(revenue) - SUM(budget)) / SUM(budget)
    display_name: 'Revenue vs Budget'
    format:
      type: percentage
      decimal_places:
        type: max
        places: 1
  - name: unique_customers
    expr: COUNT(DISTINCT customer_id)