Hi Team,
I have a pivot table that is not showing any ‘Total’ numbers. can you please help?
Screenshot attached
Hi @Cindy,
Are you utilizing any type of calculated field for your values? And if so, what is it?
I’m wondering if it has something to do with dimensions being used in a calculated field that’s defining how to aggregate the table values; this could be limiting the ability to gather a total in those cases.
Thanks for your reply.
Thanks for your reply.
Yes, I did use calculations, but they were all quite straightforward. Most formulas were:
- sum/sum-1 (for calculating increase rates)
- sum/sum (for proportions)
- simple division between values
- sum/sumif (for conditional aggregations)
Hi @Cindy,
Are these grouping the aggregations by specific dimensions though? This may limit the calculation from being able to create a ‘total’.
My dimensions are using existing data in the dataset but which paramters.
Ex: ifelse(${GroupBy1}=‘a. SHL1_final’,{SHL1_final},
${GroupBy1}=‘b. Geo Refined Logic’,{New Geo Logic},
${GroupBy1}=‘d. SHL3_final’,{SHL3_final},
${GroupBy1}=‘e. SHL4_final’,{SHL4_final},
${GroupBy1}=‘f. SHL5_final’,{SHL5_final},
${GroupBy1}=‘g. SHL7_final’,{SHL7_final},
${GroupBy1}=‘h. SHL8_final’,{SHL8_final},
${GroupBy1}=‘i. SHL9_final’,{SHL9_final},
${GroupBy1}=‘j. SHL10_final’,{SHL10_final},
${GroupBy1}=‘k. SHL11_final’,{SHL11_final},
${GroupBy1}=‘l. SHL12_final’,{SHL12_final},
null
). FYI: I’ve also tested on my side. If I remove sumif related calculations the total numbers can show up.
Please help if quicksight can provide totals with different caculated fields.
Cindy
Hi @Cindy,
So regarding the fields being used in this, like ‘SHL3_final’, ‘New Geo Logic’, ‘SHL3_final’ and so on; are those calculated as well? You mentioned something regarding sumif calculations but I’m not seeing in your example so guessing that has something to do with the above fields?
Are you handling different types of aggregations in this scenario or are they all sums?
Hi Brett,
To clarify about our data structure:
-
‘SHL3_final’, ‘New Geo Logic’, and ‘SHL3_final’ are dimensions used as row headers for aggregation
- We have one only calculated field (dimensions) that uses IFELSE functions to assign regional tags (e.g., “EMEA”, “APJ”)
-
The values section contains mostly SUMIFS/sum calculations like:
-
SUM/SUM-1 for growth rates
-
SUM/SUM for proportional analysis
-
Direct division between values
-
SUM/SUMIF for conditional aggregations
Let me know if you need any additional details.
Hi @Cindy,
Hope this message finds you well! Just checking in since this thread hasn’t received a response in a while. Were you able to find a solution to your question in the meantime, or are you still facing persistent issues? If we do not hear back within the next 3 business days, I’ll go ahead and close this topic.
Thanks!
no. The issue still persists. looking forward to your feedback on the solutions.
Hi @Cindy,
Apologies for missing your last response; so I think the issue may be more in line with dimensions being used for the calculations. For instance, if your columns are being calculated based on grouping, that will limit the ability to sum values outside of the group.
The column names are crossed out so I can’t refer to anything in particular, but if you provide the syntax for one of those calculations, I may be able to provide a better explanation.
Hi @Cindy
Did the @Brett suggested solution work or was the issue resolved? If you have any further questions, please let us know how we can assist you.
If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.
Thank you!
Hi @Cindy
Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.
Thank you!