Using Totals in Formulas in Revit Schedules

This has been a frequently asked question on the various forums over the years:
"Is it possible to use a grand total from a schedule in a formula in Revit".  The first response is always No!  Then eventually someone will mention the arcane workaround but it is not always clearly explained so I have decided to show a simple example of what the problem is, and how the workaround might be applied.

I must confess that this is not my original idea - in fact I have found references to the workaround, as far back as 2006 on AUGI by Leonard Raiz (one of the Revit Founders).  More recently (2011), Martijn de Riet published a description of how to calculate a ratio between two area types - this includes the workaround but does a few extra tricks of calculating a ratio of two different sub-totals after first separating them from one grand total, so it is not that easy to understand just the 'totals in formulas' workaround.  Many thanks to Martijn, as I originally learnt the technique from his blog posting.

The Problem With Schedule Totals

Let us suppose that you want to schedule a series of floor areas for a multistorey building and then calculate the ratio between the total of those floor areas against the site area - in Australia we call that the 'Floor Space Ratio'.  There are actually two problems that Revit presents us with here:

• Revit apparently does not allow you to insert a total of several values into a formula- it only performs the calculations on individual values in each row
• You can't create a schedule that combines 'Areas' and 'Property Line Areas' because they are different categories - this is another topic so we'll use another quick workaround to deal with that here.

In this example I will use Mass Floor Areas, but they could easily be normal Areas

The Floor Space Ratio calculation is defined as:   'Gross Floor Area' divided by 'Site Area'
It is usually represented as the 'number : 1'   eg. 2.5 : 1

Step 1

• The first step is to create a Mass Floor schedule, and add the Levels plus Floor Areas

 

• The first problem we encounter is that you cannot combine a Mass Floor schedule with Site category schedules - so you cannot include the site area value that Revit automatically calculates from your property lines

Workaround 1

• Create a shared parameter and apply it to the Project Information category

• This will be a value that you have to type in to the project information page once - and update it if the site area changes, which is obviously a technique prone to user error.  If your site area is not yet settled, and is constantly changing, I would not recommend this method.
• Once the value is in, you can add the shared parameter to your schedule just by changing the category from the drop-down list and choosing Project Information

 

• The same value for Site Area will appear in each row, so make sure not to calculate totals for this parameter.

 

Step 2

• Add a calculated parameter to the schedule, called 'Calc Ratio' (or some such) 
• Make it a 'Number' type with this formula:
  Floor Area / Site Area

• Note that each row has its own calculation of the ratio - none of which is correct.

• If you make the parameter 'Calculate Totals', the Grand Total value of the Ratio will actually be correct - but it makes for a very confusing schedule

• You could of course collapse the schedule down to one line (untick 'Itemise Every Instance'), and hide the totals - this would display the correct Ratio - but you may require the schedule to show individual floor areas

 

However, there is a way to get Revit to display all the rows of data and to perform calculations on the total value - it requires the following workaround:

Workaround 2

• In the schedule, add a new calculated parameter called 'F Area Percentage'
• This time, make it a percentage calculation - click on the 'Percentage' radio button
• Then select 'Floor Area' as the parameter to calculate 'Of'
• Leave it to calculate 'By' Grand Total

• This will list each floor area as a percentage of the total

• Create another new calculated parameter, called 'Total Floor Area'
• leave it as a Formula calculation, with type 'Area'
• Add the Formula:
  Floor Area / F Area Percentage

• This will give you the correct overall floor area in each row 
• Be sure not to calculate totals for this parameter.

 

• Now change the Ratio formula to calculate using the Total Floor Area:
  Total Floor Area / Site Area
• This will give the correct ratio in each row
• Remove the Calculate Totals setting for this parameter as the total would be a crazy value

• You can then hide the two intermediate calculations (Total Floor Area and F Area Percentage) to get a schedule showing the correct site area, floor area and ratio in every row

 

Tidy Up

• To make the schedule read a little more cleanly, you could sort it by Site Area, with a Header, and then hide the Site Area parameter column - but it only shows the value of the Site Area, not its parameter title, which is not clear enough

• Then add another Calculated Parameter at the beginning - a text type with a formula:
  "Site Area :"
• Sort by that parameter before the Site Area, and hide that one too
• It puts them on two different lines but at least it removes some repetitive information from the body of the schedule

 

 

• You could try the same with the Floor Space Ratio but unfortunately Revit does not like to sort a schedule by such a formula  - it does not show in the Sort by or Filter list (a bug perhaps? or just a special feature?)
• You could also put in a few extra text formulas (with blank headings), although they will repeat the same information

Workaround 3

If you can cope with two schedules, it is a lot easier: a simple one liner schedule for the Floor Space ratio calculation, plus a separate schedule list of all the floor areas.

• You can either create two similar schedules as described above, but collapse one of them down to one line (untick 'Itemise every instance'), and remove the calculations from the full list.
  or
• Revit makes it simpler to do this operation on Mass Floor areas because you can just create a Mass schedule (as opposed to Mass Floors) that already has a property called 'Gross Floor Area', in which it automatically calculates the total of the Mass Floors - thus you can do a direct calculation on it

I hope this explains the technique clearly.  If you want something more complex, then refer to Martijn de Riet's blog posting on how to calculate a ratio between two area types