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

[Kuaffu]

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

16

Browser

Chrome

Additional context

No response

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]

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}

[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)