PivotTable doesn't corrently count total (SUM) in case of division metric

[derichev-ozon]

Currelty Superset (v.1.5.1 and also in v.2.0.0) for Chart PivotTable (v1 and v2) doesn't corrently count total/subtotal (SUM aggregation) in case of division metric exists. For example: Let's create PivotTable, add metrics and populate as following Where is "proc" column is metric with function "SUM(fm) / SUM(gmv)" . As you can see Total (Sum) is 2.7 , but it should be 165M / 376M ~= 0.4388 . So Superset just SUM column "proc" values instead of direct division.

How to reproduce the bug

1. Create dataset "default.test_pivot", for instance using following SQL:

SELECT 34523.323 as gmv, 24523.323 as fm, '1' as shem, '1' as gr
UNION all
SELECT 325323543.323 as gmv, 125323543.323 as fm, '2' as shem, '1' as gr
UNION all
SELECT 5764545.323 as gmv, 4764545.323 as fm, '3' as shem, '2' as gr
UNION all
SELECT 45254525.323 as gmv, 35254525.323 as fm, '4' as shem, '2' as gr

2. Create Chart PivotTable v2 (visualization type = Pivot Table v2) based on dataset above.
3. Populate rows as "gr" (Simple ABC gr) and "shem" (Simple ABC shem)
4. Create metrics:
   • SUM(gmv) (Simple , Column = gmv, aggregate = SUM)
   • SUM(fm) (Simple , Column = fm, aggregate = SUM)
   • proc (Custom SQL with value SUM(fm) / SUM(gmv) )
5. In the Options area check "Show columns total" and select Aggregation functiona as "SUM"
6. Run (evaluate values for Chart)

Expected results

Total (Sum) should be 165M / 376M ~= 0.4388 . The same for Subtotal correspondingly

Actual results

Total (Sum) is 2.7 , just SUM column "proc" values instead of direct division.

Screenshots

See above.

Environment

• browser type and version: chrome 105.0.5195.102 (64bit version)
• superset version: 1.5.1
• python version: 3.8.12
• node.js version: any
• any feature flags active: any

Checklist

Checked:

• [x] I have checked the superset logs for python stacktraces and included it here as text if there are any.
• [x] I have reproduced the issue with at least the latest released version of superset.
• [x] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

If some workaround exists it will be also good.

[mayurnewase]

use pivottable 2, this one is deprecated and has few bugs which are less likely to be fixed in the future.

[derichev-ozon]

@mayurnewase Thanks for response! Actually I use exactly pivottable 2 (if you meant v2):

2. Create Chart PivotTable v2 (visualization type = Pivot Table v2) based on dataset above.

[mayurnewase]

ahh sorry missed it.

[mayurnewase]

I think this is correct behaviour to show sum of column instead of checking how its calculated, I might be wrong.

[kgabryje]

Hey @derichev-ozon! Like @mayurnewase said, it's working as intended - we're showing a sum in total and subtotals regardless of SQL behind given column. I can totally see a value in what you're expecting. I think we should treat it as a new feature request - overriding global aggregation function for specific column.

[xakbin]

Please tell me the status, this problem is very annoying

[geido]

Closing this as we are not supporting versions older than 3.X at the moment. Feel free to open a new issue if you feel this is still a problem with recent versions of Superset. Thank you!