How do I calculate the sum of a column so that I can then use that to determine the percentage of the total for a given value?
For example I want to show the percentage of total sales by store, salesperson etc.
Everything I have tried so far just returns 100% as it is only doing the calculation on a row by row basis rather than using the column total.
Nathan Waterman commented
Thanks for quick response. I don't seem to be able to sum anything that isn't related to a specific record. My sales have records for Jan and Feb for Bobby and Jeremy but my calculation only returns the percentage of sales for Jan out the line totals for Bobby and Jeremy, not the percentage of total for January.
Following your suggestion I would expect to see the denominator column showing the same value all the way down the table against each record.
This would need to be two formulas in the parent: one to sum the numerator and the other to sum your denominator. Sums are ideal so that you can control each variable since a count() will count every child that has a value in the field where you may not want to count them in either variable.
In most cases, you will want to place and IF() statement in the child table to flag the records you which to sum since not all records will be considered a "sale". This IF() will output a 1 on a sale and a 0 on everything else. Your denominator could then follow the same logic if only certain records contribute (count() is best if ALL children roll up to your metrics).
If this does not match your use case, we recommend submitting a ticket with your app specifications so that we can better investigate your needs.