Skip to main content

Calculate percentage of total of a field

  • January 3, 2023
  • 3 replies
  • 488 views
Javier_Growth_L
Forum|alt.badge.img+8

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!

    3 replies

    WilliamPorter
    Forum|alt.badge.img+19

    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.

    1. Create a separate table SalesTotal. I'm going to suggest making the primary field here a Date field (but you could change that as you need). 
    2. Let's say you're doing your report today (January 3, 2023). So you create onerecord in SalesTotal and enter today's date into the primary field.
    3. Now switch back to your SALES table. Create a linked record field linking records here to SalesTotal. I called this field "LinkedTotal" but of course call it what you like. 
    4. Switch back to SalesTotal and create a rollup field DatedTotalFromSales that looks up the value in the Sales Amount field in SALES and totals it up with the formula SUM(values).
    5. Switch back to SALES and create another field called (say) LookedUpTotalSales. This should look up the value in the field you created in step 4.
    6. Populate the LinkedTotal field by entering today's date in one field and then dragging down to populate the other two records. Now all three records are linked to the record in SalesTotal for 1/3/2023 (1 Jan 2023).
    7. Finally, create a formula field in SALES called PercentOfDatedTotal that references the lookup field. This is now a simple formula: Amount / LookedUpTotalSales. Format output of this field as a Percent with precision 1.00.

    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. 

      WilliamPorter
      Forum|alt.badge.img+19

      Sheesh. Still can't edit my own post here yet? Well here's a screen capture illustrating the above suggestion:

       

      Javier_Growth_L
      Forum|alt.badge.img+8

      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.

      1. Create a separate table SalesTotal. I'm going to suggest making the primary field here a Date field (but you could change that as you need). 
      2. Let's say you're doing your report today (January 3, 2023). So you create onerecord in SalesTotal and enter today's date into the primary field.
      3. Now switch back to your SALES table. Create a linked record field linking records here to SalesTotal. I called this field "LinkedTotal" but of course call it what you like. 
      4. Switch back to SalesTotal and create a rollup field DatedTotalFromSales that looks up the value in the Sales Amount field in SALES and totals it up with the formula SUM(values).
      5. Switch back to SALES and create another field called (say) LookedUpTotalSales. This should look up the value in the field you created in step 4.
      6. Populate the LinkedTotal field by entering today's date in one field and then dragging down to populate the other two records. Now all three records are linked to the record in SalesTotal for 1/3/2023 (1 Jan 2023).
      7. Finally, create a formula field in SALES called PercentOfDatedTotal that references the lookup field. This is now a simple formula: Amount / LookedUpTotalSales. Format output of this field as a Percent with precision 1.00.

      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. 


      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 🙂

      Terms & ConditionsAccessibility statement