I have a table with a filtered view. I am trying to get the sum of all numbers in a column? How do I achieve this? There is the footer aggregation. But I cannot seem to export that value to another table for computation purposes (for eg. to determine the % value of a field. I want to divide the number in a particular field by the sum of all values. However, how do I get that sum of all values, using sum function or otherwise)?
Hi,
You cannot export or use the footer aggregation. However, you can generate the same result in another table using nested formulas.
This will mostly require a combination of the filter(), sum() and count() function where the filter function allows you to select only records that meet certain conditions.
The exact formula depends on your particular case.
Thanks for the response. I am still struggling to get this to work. I have one table which has among other fields, two relevant fields for the purposes of this discussion- (a) Transaction_type -which is a Single Select field and users can either choose its value as ‘primary’ or ‘secondary’; (b) Score- which is a number.
What I am trying to achieve is display the sum of all the values of ‘Score’ which has Transaction_type ‘Primary’.
Alternatively, even a simple sum of all the values of ‘Score’ would do.
thanks for your help!
You need to replace the single select field with a link to table field.
-
Create a new table and name it Transaction types
-
Add 2 records to this table: primary and secondary
-
Change the type of the Transactions_type field to link to table and link to the new table
-
Select the item primary or secondary for each record.
-
Go back to the transaction types table and notice the additional field
-
Add a new field of the type formula and add the following formula
sum(lookup('Transactions','Score'))
The result is the total score for each transaction type
- Use the SUM function to calculate the sum of all values in the column.
- Export the filtered data to a separate table.
- Calculate the percentage using a formula: (Value / Sum of all values) * 100. Replace “Value” with the specific value and “Sum of all values” with the sum calculated using the SUM function.
- Apply the formula to each row in the exported data to determine the percentage.
Hello @ashleymike and @frederikdc, thank you for your posts. Is it possible to get the percentage calculated as per @pdhym request? Based on @frederikdc previous example, what I’d like to obtain is the percentage for each row: row ‘primary’ (22 on 32 = 69%) and row 'secondary" (10 on 32 = 31%). Thank you for your help!
You can divide the sum of the scores by the total score that can be reached. I assume that this depends on the transaction type. Assume that the Transactions_type table has a field Total points
The formula would be (sum(lookup('Transactions','Score')) / (count(field('Transactions') * field('Total points')) * 100)
- (sum(lookup(‘Transactions’,‘Score’)) → gets the total score for each transaction type
- (count(field(‘Transactions’)) → counts the number of transactions of each type
- field(‘Total points’) → holds the number of points that can be earned for each type. You can hard code this if it is the same for all types.
Thank you so much @frederikdc for your answer and proposal !
I perfectly understand the way to get the sum for each transaction type.
What is still unclear for me though is the mean to obtain the total of a column in order to calculate percentage…
Let me take another example: there is a group of 50 people learning foreign languages. The number of people can vary with time. Each individual learns only 1 language (German, French and Spanish).
-
For each language, I’d like to know the number of learners and the percentage vs. total number of people.
-
More difficult option: same question but in this case, each individual can learn up to 3 languages (German, French and Spanish).
Thank you for your help!
You can use a Count field for this that counts the number of Persons linked to that Language.
The percentage is harder to calculate since none of the tables (Languages and Persons) can tell with a formula how many records there are in the Persons table. The Languages table can only calculate the number of persons for each language.
Thank you @frederikdc ! I understand this point.
I think there were a feature idea recently posted on this subject (to have the possibility to get the sum at column (field level?) as a variable to use in formulas)?
@olgatrykush, you’re maybe aware…
In this case, can you redirect to this request so that I can add my view and vote?
Many thanks !
