Hi there,
I am trying to use a custom field with sumOver() formula in aggregation for pie chart, but the aggregation is incorrect.
custom_total formula
sumOver(total,[{event_date},{channel_id},{rule_name},{shared_with}],PRE_AGG)
It shows the correct number in the pivot table, but not in the pie chart when using the sum of {sumover} field.
not sure what and how QS came up with these numbers. Any suggestion on how I can solve the problem?
@neelay Thanks for your question. If you don’t get a reply soon from one of our community members, we’ll reach out to our internal experts on Tuesday to see if we can get a reply for you.
Hope you had a great weekend!
Hi @neelay,
Can you try to change the “Sum” in your “Value” field well to “Min”, i.e. sumover (Min) instead of sumover (Sum)?
@David_Wong If I do min, it’s picking the lowest value in the group, so for the above data, I will get 1 for each ICD-10, ICD-9, and FDA
which makes sense, as I am asking it to find min when I do group by.
What value are you expecting for ICD-10? 1079?
I think you need a different calculated field for your donut chart. If your first calculated field is called sumover, then the new one should be:
sumOver({sumover}, [{rule_name}], PRE_AGG)
Hi @David_Wong
What value are you expecting for ICD-10? 1079?
Yes
I have tried that as well, It didn’t work. I have also played with different levels as well to see if I can get lucky and get the intended output.
this is the output of sumOver({sumover}, [{rule_name}], PRE_AGG)
Did you set the aggregation to Min?
@David_Wong yes. the attached screenshot is for that.
I have also tried other options as well
That’s weird. I was able to get the correct value by just putting sumover in the Value field well and using Sum as aggregation.
If you’re not getting this result, I think it means you have some data in your dataset which is not being displayed in your pivot table. Do you have more fields in your dataset that aren’t in your pivot table? Can you select your pivot table and show a screenshot of the field wells?
@David_Wong Are your values in sumover column calculated with sumover custom formula?
or you just put it there as static values.
@nshah-quicksight I just copied the values from your screenshot to create my own dataset.
@David_Wong thanks for trying to help me… really appreciate the effort.
My issue is the aggregation of value which was derived with sumOver() formula.
@Kristin Can you please help me?
I might also be misunderstanding your question. Is your sumover field the same as your custom_total field? The screenshot of your donut chart shows the sumover field in your field well but you also wrote custom_total.
Thanks @David_Wong for fielding these questions. 
@neelay Thanks for reaching out to the QuickSight Community. We’d love to help you find a solution. Let us know your thoughts on David’s question above.
I hope both of you get everything from this
BTW, all 4 pie charts are wrong, though 1st is showing the expected result, the other 3 are wrong as well as showing the unexpected result.
We already went through sum(sumover()) example… So I didn’t include them in the screenshot.
You said that the 1st pie chart is showing the expected result. In what way is it wrong?
Do you not want your donut chart to show the total for each rule? Are these the totals that you’re expecting?
ICD-10 code: 1078
ICD-9 code: 3
FDA Code: 1212
@David_Wong Because of the shared_with column, I have duplicate rows (they are not true duplicates),
The numbers appeared “correct” in the 1st pie chart but they are not, ICD-10 is off by 5
If I hide the shared_with column, the total will sum the duplicate values, and that’s why I using SUMOVER. only custom_total shows the correct value…
I am asking why sum(custom_total) is not working.
if sumover is not intended to work this way, I am also thinking what’ is the use case of sumover then?
OK, I understand your problem now. You need to calculate your total like this:
There are 2 nested aggregations here. The inner aggregation says that for every combination of rule_name, event_date and channel, take the min of total. This effectively removes your duplicates. The outer aggregation then takes of sum of the mins to give you a total of 1073 for ICD-10.
it didn’t work for me either…
min(custom_total_3) where custom_total_3 = sumOver(total,[{rule_name},{event_date},{channel_id}],PRE_AGG)
still show 1 in pie chart.
@David_Wong here is the dataset
if you want to play with it
