axmo: "1.0"
name: Actual versus budget
description: >-
  Recipe — one series fed from two tables of different shapes. Actuals arrive as a long table (one row per
  department and month); the budget arrives as a wide table (months across the columns). Each loader fixes the
  Version element it supplies. Versions are never added together; the variance compares them.
dimensions:
  Departments:
    type: categorical
    layers: [Top_Departments, Department]
    tree:
      All_Departments:
        - Sales
        - Marketing
        - Operations
  Versions:
    type: categorical
    layers: [Top_Versions, Version]
    tree:
      All_Versions:
        - Actual
        - Budget
  Time:
    type: sequential
    sequentialType: axmo_time
    parameters:
      selected_levels: [month, quarter]
      start: "2027-01"
      end_mode: end_point
      end: "2027-03"
series:
  Cost:
    type: input
    description: Operating cost, EUR, by department, version and month. Versions are not added.
    dimensions:
      - Departments:
          rollUp: SUM
      - Versions:
          rollUp: NONE
      - Time:
          rollUp: SUM
  Variance:
    type: calculated
    description: Actual less budget, EUR. Positive means overspent.
    formula: Cost[Actual] - Cost[Budget]
    dimensions: [Departments, Time]
  Variance %:
    type: calculated
    description: Variance as a share of budget, recalculated from the totals at every level.
    formula: Variance / Cost[Budget]
    dimensions:
      - Departments:
          rollUp: FORMULA
      - Time:
          rollUp: FORMULA
dataSources:
  Actuals:
    sourceType: excel-range
    rangeRef: Actuals!A1:C10
    hasHeaders: true
    shape: table
  Budget:
    sourceType: excel-range
    rangeRef: Budget!A1:D4
    hasHeaders: true
    shape: table
    columnLayout: dimension
    columnsDimension: Time
    bandStartMode: after
    bandStartColumn: Department
    bandEndMode: end
dataLoaders:
  Load actuals:
    type: seriesLoader
    source: Actuals
    target: Cost
    loadMode: table
    valueColumn: Amount
    duplicateHandling: BLOCK
    dimensionMappings:
      Departments: { column: Department }
      Time: { column: Month }
    fixedCoordinates:
      Versions: Actual
  Load budget:
    type: seriesLoader
    source: Budget
    target: Cost
    loadMode: table
    valueColumn: _Value
    duplicateHandling: BLOCK
    dimensionMappings:
      Departments: { column: Department }
      Time: { column: _HeaderDimElement }
    fixedCoordinates:
      Versions: Budget
outputRanges:
  Budget report:
    cell: Report!A1
    rowDef:
      blocks:
        - dimension: Departments/Department
        - dimension: Departments/Top_Departments
        - series: Actual := Cost[Actual]
          format: "#,##0"
          newLine: true
        - series: Budget := Cost[Budget]
          format: "#,##0"
        - series: Variance
          format: "#,##0"
        - series: Variance %
          format: 0.0%
    colDef:
      blocks:
        - dimension: Time/Month
        - dimension: Time/Quarter
data:
  Actuals!A1:C10:
    values:
      - [Department, Month, Amount]
      - [Sales, "2027-01", 52000]
      - [Sales, "2027-02", 49500]
      - [Sales, "2027-03", 55300]
      - [Marketing, "2027-01", 21000]
      - [Marketing, "2027-02", 26500]
      - [Marketing, "2027-03", 19800]
      - [Operations, "2027-01", 88000]
      - [Operations, "2027-02", 86200]
      - [Operations, "2027-03", 91500]
  Budget!A1:D4:
    values:
      - [Department, "2027-01", "2027-02", "2027-03"]
      - [Sales, 50000, 50000, 52000]
      - [Marketing, 22000, 22000, 22000]
      - [Operations, 87000, 87000, 90000]