Incorrect totals - Table - SumOver function

Hello,

I have a calculated field in a table that looks like this:

sumOver(sumIf(abs(Qty), {Key Figure} = ‘History’), [Date, {Brand}, Market]) - sumOver(sumIf(abs(Qty), {Key Figure} = ‘Selling_Qty’), [Date, {Brand}, Market])

The problem is that I need to display totals and these are somehow incorrect, regardless If I apply a filter to any of the fields - Date, Brand or Market or leave the table unfiltered
image

I would like the totals to dynamically change according to my filters. I can not think of any reason where does QuickSight get these numbers from?

Hi, From the screenshot it is not clear how the calculation for total is incorrect. Can you plz share few sample records and screenshot(including all relevant columns) of what you expect and how quicksight is currently displaying totals?

Regards,
Karthik

Hi Karthik,

from the screenshot I attached before it’s clear how the total is incorrect - I tried downloading the table contents and checked the totals in Excels and the number is different then the one that is displayed in QS table. The number in “Forecast” column (1715) is not matching the total sum of the rows within that column.

Thanks

Totals in QuickSight are not simply a sum of the individual values. For example, if the measure is aggregated using distinctCount, Total will show distinctCount over the entire dataset which is not a sum of individual distinctCounts.

Thank you Tatyana, is there any way to display totals as sum of individual Values in a table?

Hello,
If the firstRow (I cant see because the screenshot doesn’t show it well) is the Total added when you select in the Visual settings of the table “Show Total”, then you might want to create a second column:
sumOver(min(Forecast vs…),[Date, {Brand}, Market],POST_AGG_FILTER)

And, check if the total is correct now.