Calculations for Total

Question & Answer
1

I am looking to Divide a field by the total. Example: So the total should not take in the dimensions in the visual. I tried creating a calculated field that filters for selective dimensions but not all in the sheet/analysis. So, looking to calculate total of for example. lets say I have a column called Subject, and another column called MArks/score. Now I am trying to create a calc field that will do the sum of Marks for all subjects. I tried SUM OVER and it is not helping.

2

Hi @ravists2002

To divide a field by the total, you can use the QuickSight aggregation function called “TOTAL.” The “TOTAL” function allows you to calculate the sum of a field across all dimensions in a visualization, ignoring any dimension-based filtering or grouping.

To calculate the total of the “Marks/score” column for all subjects, you can create a calculated field using the following formula:

SUM(Marks) / TOTAL(SUM(Marks))

Here’s how you can create this calculated field:

  1. Open your analysis or sheet in QuickSight.
  2. Click on the “Data” pane on the left-hand side.
  3. Under the “Fields” section, click on the “+” button to create a new calculated field.
  4. In the formula editor, enter the formula mentioned above: SUM(Marks) / TOTAL(SUM(Marks)).
  5. Provide a name for your calculated field, such as “Marks Total Ratio.”
  6. Click “Create” or “Save” to create the calculated field.

Now, you can use this calculated field in your visualizations. It will display the ratio of the sum of marks for each subject to the total sum of marks across all subjects, regardless of any dimensions in the visualization.

Please note that the “TOTAL” function calculates the sum across all dimensions, so if you have any filters applied at the dataset or analysis level, they will still be considered in the total calculation. However, any filters or dimensions applied within the visualization will not affect the total calculation.

I hope this helps!

1 Like
3

Hi @ravists2002

Hope you are able to resolve the problem with the solution provided by @Blenner .
I am marking the reply as “Solution,” but let us know if this is not resolved.

Thanks for posting your questions on the QuickSight CommunityQ&A Forum!

4

Hi, in quicksight, I could not find the aggregation function called “Total”. Could you kindly share where I could find it?

Total Function _ 11.24.2023_V3
Total Function _ 11.24.2023_V3769Ă—136 17.6 KB