Correct group-level aggregation for ratio and percentage metrics in the Interactive Pivot Table

What is the problem or goal you're trying to solve or accomplish?

The customer builds Interactive Pivot Table charts that include percentage-change and ratio metrics (e.g. period-over-period % change in covers) alongside additive metrics, grouped across a multi-level row hierarchy such as METRO > METRO_AREA > RESTAURANT_NAME. They need the percentage columns to show the correct value at every level of the hierarchy — group rows and the grand total — not only on leaf rows.

Today the query returns one row per leaf, and group rows are computed in the browser by applying a single aggregation function to the already-computed child percentages. This is correct for additive metrics but not for ratios, because aggregating pre-computed ratios discards the denominators they were derived from.

The impact is a correctness issue, not a precision one. Validated on a controlled dataset:

Two things to highlight:

• The displayed value can point in the wrong direction. EUROPE declined 28.6% but the chart reports growth of 50% (or 25% under Average). A dashboard reader concludes the opposite of the truth.

• The grand total row is the most prominently read cell in a pivot table, and it reports 110% — a value that is not merely imprecise but outside any plausible range, with no warning shown.

Notably, the correct value is already derivable from data the chart displays correctly on the same row: SUM(COVERS_CURR) and SUM(COVERS_PREV) roll up accurately at every level (1.61k / 1.1k at the CANADA row; 2.11k / 1.8k at the total). No additional database round-trip is required — only the ability to evaluate the metric's formula from those aggregated values at each level.

How are you solving it currently?

The customer's original mitigation was to disable the % columns from the column selector so they could not be unselected — which prevents accidental changes but does not produce a correct value.

Validated alternatives, none of which yields a correct figure:

• Omitting the % metric from the pivot and exposing the numerator and denominator as separate metric columns. Both roll up correctly at all levels, so the reader can compute the ratio manually. This is the least-bad option and is the current recommendation.

• Building a separate chart at the specific grain where a group-level percentage is needed, so the database computes it.

• Setting the column's Value Aggregation to None, which suppresses the column in the grid. This removes the misleading group values but also removes the correct leaf-level values. It is reversible and persists on save, but the column remains listed in Choose Columns, so any viewer can re-enable it and restore the misleading display.

Important: the existing per-column Value Aggregation control is not a workaround. It functions exactly as designed and persists correctly on save, but none of its eight options (None, Average, Count, First, Last, Max, Min, Sum) produces a correct group-level ratio. Sum and Average are both wrong and both flip the sign in the EUROPE case; Min/Max/First/Last return a single child's value; Count is not a percentage. The gap is a genuinely absent capability, not a misconfiguration.

What is your recommended solution?

The customer's ask is for percentage and ratio columns to display the mathematically correct value at group and total levels. Two implementation directions, in increasing generality:

1. A ratio-aware aggregation type. Extend the Value Aggregation options with a mode that takes a numerator and a denominator column, aggregates each independently across the group's children, and divides. This is the narrower, cheaper fix and covers the large majority of real cases (% change, conversion rates, averages per unit, margins).

2. Per-level derived columns. Allow a metric column to be defined as a formula over other aggregated columns, re-evaluated at each row-group level. This subsumes option 1 and also fixes the related problem of averages (SUM(AVG_CHECK) style columns) rolling up meaninglessly.