Jan 03, 2023 10:16 AM
Hi there Airtable folks!
I'm trying to wrap my head around a simple calculation - but I'm not sure how this can be done.
I'd like to calculate the % of total of a given column, I know I can input a constant number into the formula but the table will increase it records, so it has to be calculated.
Essentially I want to perform the following, take each number and divide it by the total.
Is there a way to do that?
Thanks a lot!
Jan 03, 2023 11:16 AM
Yes, this is a bit tricky. At least I myself do not know a simple way to do this. In FileMaker and some other systems I've worked with, there's a function that dynamically captures various totals for the current record set. It's not a record-based value, in other words, but a record-set based value. But I'm not aware of a function in Airtable that does exactly this. (Be delighted to be corrected.)
Here's a non-simple way. Let's say the table you're trying to do these calculations in is called SALES. Let's assume you have three records to report on in SALES with the values in Amount field of 5, 10, and 35, and records in this table are already filtered to show just these three records.
And that should do the trick. The trick is, when you run your report, you have to do two things "by hand": first, create a dated record in SalesTotal for today's date; then, after you filter your values in SALES on some other field, you populate the LinkedTotal field (with today's date).
Make sense? It's a bit of back and forth but it does the job.
Jan 03, 2023 11:19 AM
Sheesh. Still can't edit my own post here yet? Well here's a screen capture illustrating the above suggestion:
Jan 18, 2023 07:42 AM
Hi William! Thank you very much for your thorough and detailed reply.
I will follow your instructions, I think I get the overall idea but I need to test it with the data model I've created for this. Appreciate your time and effort!
I'll mark this as a solution once I try it out 🙂