Re: Create % formula for column groups

867 0
cancel
Showing results for
Did you mean:
6 - Interface Innovator

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 4
18 - Pluto

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

11 - Venus

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

6 - Interface Innovator

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!

11 - Venus

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.