Apache Superset Issue with Incorrect Subtotal and Sum Calculation of Completion Percentage in Pivot Table

Bug description

we created a pivot table chart to show the completion percentage of sales area, and enabled the subtotal and sum function, the target and actual amount is correct, but the subtotal and sum of completion is wrong, what I'd like to get is " sum(actual_amount) / sum(target_amount)", however, the pivot table sum the completion percentage. anybody met this issue?

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.9

Node version

Browser

Chrome

Additional context

No response

Checklist

[x] I have searched Superset docs and Slack and didn't find a solution to my problem.
[x] I have searched the GitHub issue tracker and didn't find a similar bug report.
[x] 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.

Comment From: dosubot[bot]

Hey @Kuaffu! I'm here to help you with any bugs, questions, or contributions you have for Superset. Let's work together to solve this issue!

To address the issue of incorrect subtotal and sum calculation of completion percentage in a pivot table, you can use the "Sum over Sum" aggregator template. This aggregator calculates the ratio of the sum of two fields, which is what you need for calculating "sum(actual_amount) / sum(target_amount)".

Here's how you can implement it:

const { sumOverSum } = aggregatorTemplates;

// Use the sumOverSum aggregator with the actual_amount and target_amount fields
const completionPercentageAggregator = sumOverSum(['actual_amount', 'target_amount']);

This approach ensures that the completion percentage is calculated as the sum of actual amounts divided by the sum of target amounts, rather than summing the individual completion percentages ^[1].

To continue talking to Dosu, mention @dosu.

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}

Comment From: Kuaffu

Hey @Kuaffu! I'm here to help you with any bugs, questions, or contributions you have for Superset. Let's work together to solve this issue!

To address the issue of incorrect subtotal and sum calculation of completion percentage in a pivot table, you can use the "Sum over Sum" aggregator template. This aggregator calculates the ratio of the sum of two fields, which is what you need for calculating "sum(actual_amount) / sum(target_amount)".

Here's how you can implement it:

const { sumOverSum } = aggregatorTemplates;

// Use the sumOverSum aggregator with the actual_amount and target_amount fields const completionPercentageAggregator = sumOverSum(['actual_amount', 'target_amount']); This approach ensures that the completion percentage is calculated as the sum of actual amounts divided by the sum of target amounts, rather than summing the individual completion percentages [1].

To continue talking to Dosu, mention @dosu.

Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other

This only solves the current issue. When actual_amount or target_amount is not dragged, it still cannot calculate sum(actual_amount) / sum(target_amount)

Comment From: ng22t7a

hi @Kuaffu, have you find out how to fix this?

Comment From: Kuaffu

@ng22t7a Sorry for not resolving this issue. I'm not particularly familiar with Superset, but I've seen similar situations in other BI tools. When it comes to totals or subtotals involving calculated fields, it often requires generating additional SQL queries to handle the computation.

Comment From: rusackas

Still open to PRs here if anyone wants to dig into the Pivot Table code... this is not a pressing issue for committers from the Preset team, but we're happy to review solutions!