Pivot Table Grand Total sums percentages instead of re-calculating the metric (Server-side aggregation failed)

[mudeshankarnaik]

Bug description

I am using Apache Superset to build a DataMart with 200+ metrics. When using the Pivot Table, the Grand Total row is performing a simple SUM() of the rows in the frontend (Javascript) instead of re-calculating the metric using the provided SQL expression. This results in incorrect "Partial Sums" for ratios (e.g., $10% + 20% = 30%$ instead of the correct weighted average).

How to Reproduce
Create a metric: SUM(numerator) / NULLIF(SUM(denominator), 0).

Add this metric to a Pivot Table.

Enable "Show Totals".

Observe that the Total row sums the individual row percentages.

Expected Behavior
The Total row should re-execute the metric's SQL formula against the total context (Total Numerator / Total Denominator), similar to how "Partial Sums" work in tools like Qlik.

Actual Behavior
The total is a simple summation of the values displayed in the table rows.

Environment
Superset version: 5.0.0 (or your specific version)

Database: ClickHouse

Feature Flags enabled: ENABLE_BACKEND_PIVOT_TOTALS, GENERIC_CHART_AXES

Screenshots/recordings

No response

Superset version

5.0.0

Python version

3.9

Node version

16

Browser

Chrome

Additional context

I have confirmed that ENABLE_BACKEND_PIVOT_TOTALS = True is set in superset_config.py, but the legacy Pivot Table plugin does not appear to respect this flag.

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Hi @mudeshankarnaik! I'm Dosu and I’m helping the superset team.

This is a known limitation in Superset's pivot table implementation. A few clarifications:

The ENABLE_BACKEND_PIVOT_TOTALS feature flag doesn't actually exist in Superset. Pivot table totals are controlled by form data parameters (rowTotals, colTotals, etc.) and are always processed on the backend using pandas in client_processing.py. Additionally, there's only one pivot table plugin now (pivot_table_v2) — the legacy v1 was migrated away in June 2023.

The underlying issue: Superset treats percentage/ratio columns as regular numeric columns during aggregation, so totals sum the displayed values instead of recalculating the formula. This affects pivot tables, table charts with summary rows, and non-additive metrics like COUNT_DISTINCT.

Workaround: Define your ratio metrics at the dataset level as custom SQL metrics. This ensures the aggregation happens at the SQL level before display.

Fix in progress: PR #34592 proposes a multi-query architecture that would execute separate queries for each grouping level to ensure mathematically correct totals. It remains open but unmerged. There's also an early-stage proposal for an AntV S2-based pivot table that could address this more comprehensively.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}

   

[manojshetty2004]

Hi @sadpandajoe ,

I was able to reproduce this issue locally and confirm that totals for ratio metrics are currently computed as a simple aggregation of displayed values, which leads to incorrect results for non-additive metrics.

From what I understand, this happens because the pivot table loses the original SQL context (e.g., numerator/denominator) and treats metric outputs as plain numeric values during aggregation.

I’d like to explore working on this. A couple of possible approaches I’m considering:

1. Multi-query approach to recompute totals at each aggregation level
2. Leveraging SQL features like GROUPING SETS (where supported) to compute correct totals at the database level

Before proceeding, I’d love to get feedback on whether this aligns with the project direction or if there’s any preferred approach for handling non-additive metrics in pivot tables.

Thanks!