How can we implement Tableau Fixed LOD style calculations in cube

[gh-npuvvala]

Problem

We are trying to implement a Tableau Fixed LOD style calculation where the numberator or the denominator stay constant and only interact with defined filters.
In cube we are trying to do that with a combination of pre aggregations, multi stage, exclude and keep only tags but are not getting accurate results.
We want to understand if there is any other was we can implement that with cube.

[paveltiunov]

Thanks for the detailed description! This is a first-class, named pattern in Cube — you're most of the way there with multi-stage measures, but exclude and "keep only" tags (i.e. grain.exclude/grain.include) aren't quite the right tool for a fixed-grain calculation. What you want is grain.keep_only.

• grain.keep_only groups the inner (fixed) aggregation by only the dimensions you list, ignoring whatever dimensions the outer query groups by — this is the direct equivalent of Tableau's {FIXED [dims]: agg}.
• If the fixed calculation also needs to ignore one of the query's filters (Tableau's FIXED-ignores-context-filter behavior), pair it with filter.exclude.

See the grain reference and the "Percent of total (fixed dimension)" example in the docs.

I validated this against a running Cube instance (1.7.19, Tesseract planner) with:

cubes:
  - name: orders
    sql_table: orders
    dimensions:
      - {name: id, sql: id, type: number, primary_key: true}
      - {name: customer, sql: customer, type: string}
      - {name: order_month, sql: order_month, type: string}
    measures:
      - {name: amount, sql: amount, type: sum}
      - name: customer_total_amount   # Tableau {FIXED [Customer]: SUM([Sales])}
        sql: "{amount}"
        type: sum
        multi_stage: true
        grain:
          keep_only: [customer]

Querying amount and customer_total_amount grouped by customer, order_month:

customer	order_month	amount	customer_total_amount
A	Feb	50	150
A	Jan	100	150
B	Jan	30	50
B	Mar	20	50
C	Feb	200	200

customer_total_amount stays fixed at the per-customer total across every order_month row for that customer, instead of being re-aggregated to the finer grain — matching FIXED LOD semantics.

---

Generated by Claude Code

[gh-npuvvala]

can you please confirm if these kinds of calculations are supposed to be on cube or if they are intended to be on BI tools

[gh-npuvvala]

• name: count_requestid_ht
  multi_stage: true
  sql: "{total_requests_received}"
  type: count_distinct
  grain:
  keep_only:
  - area
  - sample_received_day
  - test_reported_based_on_projectid
  - accession_site
  - region
  - territory
  - cancer_type
  - account_name
  - npi
  - order_type
  - gh_requesttype
  - u_ghcancelmasterid
  - requeststatus
  filter:
  exclude:
  - pc_type
  - hold_type
  - sfdc_case_status
  - subject_normalised
  - exception_flag
  title: Total Requests Received (FIXED - Grain + Filter Exclude)

[sakshipatil-hue]

@gh-npuvvala From a data architecture perspective, these calculations are absolutely intended to be modeled in Cube!

Cube acts as the semantic layer, meaning its primary job is to serve as the "single source of truth" for your metrics. If you build this Fixed LOD calculation in a BI tool like Tableau, that logic is locked into Tableau. If your team ever connects a different BI tool, an embedded app, or an AI agent to your database, you would have to rewrite that complex logic all over again.

By defining the multi-stage fixed grain in Cube, you write the logic once. Then, any downstream BI tool can simply query count_requestid_ht as a standard metric and get the exact same accurate numbers without needing to write tool-specific LOD formulas.