Skip to main content

Create % formula for column groups

  • July 1, 2023
  • 4 replies
  • 30 views
D
Forum|alt.badge.img+7

I need some help! I'm trying to figure out how to create a formula to calculate the % of column groupings total.  

As you can see by the picture.  The total building size is 997,821 (SUM at the bottom).  Each year is grouped together showing the SUM of total square footage of leases expiring that year.  In 2028, there are five records (leases expiring) totaling 19,070 square feet.  How can I create a new column in my pivot table that calculates SUM of the Year (19,070 RSF) and divide it by the total building size ( SUM at bottom 997,821 RSF) to get 1.9%, for each grouped year?

Unfortunately, when i try to create a new column with the formula ({RSF Total} / SUM({RSF Total})) * 100, it does not work.  Is this because my RSF Total column is a Rollup field? Thanks!

4 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Hmm, the only way I know to deal with this is to:
1. Change the "Lease Expiring Year" field to a linked field to another table (Which creates another table)
2. In that new table, create a rollup field with the formula `SUM(values)`
3. In your original table, create a lookup field to display the sum
4. In your original table, create a formula field with the formula you described, updated to use the value from step 3

Adam, Airtable Consultant - thetimesaving.company
S
Forum|alt.badge.img+24
  • Sho
  • Inspiring
  • July 1, 2023

The charming color of the year will be lost, but as @TheTimeSavingCo's idea, it seems the only way to have it totaled in the Year Table

D
Forum|alt.badge.img+7
  • David_Marks
  • Author
  • Participating Frequently
  • July 1, 2023

Hmm, the only way I know to deal with this is to:
1. Change the "Lease Expiring Year" field to a linked field to another table (Which creates another table)
2. In that new table, create a rollup field with the formula `SUM(values)`
3. In your original table, create a lookup field to display the sum
4. In your original table, create a formula field with the formula you described, updated to use the value from step 3


Thanks Adam! I'm, unfortunately having an issue getting this to work right.  I followed the steps exactly, but am still having an issue. 
When I follow step #2, the rollup field only totals the SUM of the group.  

The second attachment is my goal.  Although I need this column to be a formula to automatically total the entire building RSF of 996,448.  Any other ideas? Thanks in advance!

2 Attachments
S
Forum|alt.badge.img+24
  • Sho
  • Inspiring
  • July 2, 2023

Oh, you need a total for all years
Try the following steps

  1. Add a record named "0" (Zero year) to the year table for the total.
  2. Add a link record field to the year table named "YearTotal" in the "Rollover" table. 
  3. Link the "0 (Zero year)" record to the "YearTotal" linked record field of all records in the "Rollover" table
    (Automation is required)
  4. Add a Rollup(Sum(values)) field for "YearTotal" to the year table.
  5. Then add a linked records field to the year table named "YearTotalLink" and link all records to 0 (Zero year).
    (Automation is required)
  6. Add a lookup field named "Rollover RSF" to the year table.
    and Get the total value by referring to the "YearTotal" field of the "YearTotalLink" record link field.
  7. change the "Rollover RSF" field in the "Rollover" table to a Lookup field and refer to the "Rollover RSF in the year table.

Now you will be able to get the total value of "Rollover RSF" for each rows

I hope my English isn't getting confused...

Terms & ConditionsAccessibility statement