dosubot[bot] commented on issue #38674: URL: https://github.com/apache/superset/issues/38674#issuecomment-4066762404
<!-- Greeting --> Hi @mudeshankarnaik! I'm [Dosu](https://go.dosu.dev/dosubot) and Iām helping the superset team. <!-- Answer --> 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`](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/charts/client_processing.py#L257-L278). Additionally, there's only one pivot table plugin now (`pivot_table_v2`) ā the legacy v1 was [migrated away in June 2023](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/migrations/versions/2023-06-08_09-02_9ba2ce3086e5_migrate_pivot_table_v1_to_v2.py). **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](https://github.com/apache/superset/issues/32260#issuecomment-2658808150), [table charts with summary rows](https://github.com/apache/superset/issues/37627#issuecomment-3839749024), and [non-additive metrics like COUNT_DISTINCT](https://github.com/apache/superset/issues/36165#issuecomment-3546090782). **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](https://github.com/apache/superset/pull/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](https://github.com/apache/superset/issues/38586) that could address this more comprehensively. <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=brand-link).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/b5cc71b6-e359-467c-9213-29510c73a735?feedback_type=great_response&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/b5cc71b6-e359-467c-9213-29510c73a735?feedback_type=irrelevant_answer&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/b5cc71b6-e359-467c-9213-29510c73a735?feedback_type=incorrect_sources&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/b5cc71b6-e359-467c-9213-29510c73a735?feedback_type=too_verbose&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/b5cc71b6-e359 -467c-9213-29510c73a735?feedback_type=hallucination&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-hallucination) | [Report š](https://app.dosu.dev/response-feedback/b5cc71b6-e359-467c-9213-29510c73a735?feedback_type=bug_report&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-bug_report) | [Other](https://app.dosu.dev/response-feedback/b5cc71b6-e359-467c-9213-29510c73a735?feedback_type=other&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-other)</sup> [](https://app.dosu.dev/a28d3c7e-a9d3-459e-9fb6-3a6f9ff4f357/ask?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=chat-badge) [](https://cursor.com/link/prompt?text=This%20is%20a%20%2A%2Aknown%20limitation%2A%2A%20in%20Superset%27s%20pivot%20table%20implementation.%20A%20few%20clarifications%3A%0A%0A%2A%2AThe%20%60ENABLE_BACKEND_PIVOT_TOTALS%60%20feature%20flag%20doesn%27t%20actually%20exist%2A%2A%20in%20Superset.%20Pivot%20table%20totals%20are%20controlled%20by%20form%20data%20parameters%20%28%60rowTotals%60%2C%20%60colTotals%60%2C%20etc.%29%20and%20are%20always%20processed%20on%20the%20backend%20using%20pandas%20in%20%5B%60client_processing.py%60%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/charts/client_processing.py%23L257-L278%29.%20Additionally%2C%20there%27s%20only%20one%20pivo t%20table%20plugin%20now%20%28%60pivot_table_v2%60%29%20%E2%80%94%20the%20legacy%20v1%20was%20%5Bmigrated%20away%20in%20June%202023%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/migrations/versions/2023-06-08_09-02_9ba2ce3086e5_migrate_pivot_table_v1_to_v2.py%29.%0A%0A%2A%2AThe%20underlying%20issue%3A%2A%2A%20Superset%20treats%20percentage/ratio%20columns%20as%20regular%20numeric%20columns%20during%20aggregation%2C%20so%20totals%20sum%20the%20displayed%20values%20instead%20of%20recalculating%20the%20formula.%20This%20affects%20%5Bpivot%20tables%5D%28https%3A//github.com/apache/superset/issues/32260%23issuecomment-2658808150%29%2C%20%5Btable%20charts%20with%20summary%20rows%5D%28https%3A//github.com/apache/superset/issues/37627%23issuecomment-3839749024%29%2C%20and%20%5Bnon-additive%20metrics%20like%20COUNT_DISTINCT%5D%28https%3A//github.com/apache/superset/issues/36165%23issuecomment-3546090782%29.%0A%0A%2A%2AWorkaround%3A%2A%2A%20D efine%20your%20ratio%20metrics%20at%20the%20dataset%20level%20as%20custom%20SQL%20metrics.%20This%20ensures%20the%20aggregation%20happens%20at%20the%20SQL%20level%20before%20display.%0A%0A%2A%2AFix%20in%20progress%3A%2A%2A%20%5BPR%20%2334592%5D%28https%3A//github.com/apache/superset/pull/34592%29%20proposes%20a%20multi-query%20architecture%20that%20would%20execute%20separate%20queries%20for%20each%20grouping%20level%20to%20ensure%20mathematically%20correct%20totals.%20It%20remains%20open%20but%20unmerged.%20There%27s%20also%20an%20early-stage%20%5Bproposal%20for%20an%20AntV%20S2-based%20pivot%20table%5D%28https%3A//github.com/apache/superset/issues/38586%29%20that%20could%20address%20this%20more%20comprehensively.) [](https://go.dosu.dev/discord-bot?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=join-discord) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/issues/38674) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
