Just making sure... no easy way of having a field with percentage of sum, correct? 🤔

[Phil_G]

Hi there! :grinning:

Thanks in advance for taking the time to help me out.
I’m new to airtable and I’m currently evaluating if I can use it for my small business to do the budgeting for my projects.

I actually think what I’m looking for is too much of a spreadsheet functionality which isn’t supported by airtable as a database tool. But I would love to be wrong because it would make my life so much easier if it was possible:
I’m trying to calculate the costs of each of my departments and usually just have a long list of items and staff. Some fields I need to be the percentage of the total costs (and update automatically if the total sum changes).
For instance I would like to have a field with contingency costs of 5% of the total sum.
Am I right assuming that there is no easy formula that can do that? And I would have to follow a complicated workaround with rollup fields and a linked table instead, because you can’t use summary values in formula fields?

I feel like I’m really in a pickle as this seems to be the only functionality I’m missing at this point, but it’s a crucial one. Using Google Sheets instead would easily allow me to create such function but other things not so much (client lists, staff lists with infos, gantt charts, etc.)

Thanks so much for helping a beginner out :slightly_smiling_face:

• Phil
  

[Rupert_Hoffsch1]

Hi Phil,

as you’ve said yourself already you’re approaching Airtable still very spreadsheet-like :slightly_smiling_face: Indeed linking records and using rollup is usually the best way to go with the big advantage that your data is more structured compared to a spreadsheet.

It’s hard to tell from your screenshot, but I would create a dedicated table for departments and for the different items and staff. These items and staff are then linked to their respective departments and you would rollup whatever you have in terms of costs. In that table you can then also easily add 5% on the total sum with a formula field.

Hope that helps!
Rupert

[Phil_G]

Hey Rupert!

Thanks so much for getting back to me and confirming my worries!
I think it’s probably too complex for me to set it up and this point and would require too much of a learning phase. The whole “linked-fields-and-rollups”-extravaganza :winking_face: is just a little bit over my head at the moment :disappointed_relieved: :disappointed_relieved: . It seems rather complex to me to set up 2 separate tables and link them considering I really only have two items that aren’t fixed costs but a percentage of the total…

Gosh… I’m really bummed out about this because I just love the UI and usability of airtable and find it much more appealing than Google Sheets. Do you know if there’s a step by step guide somewhere to achieve what I’m trying to do?

Again, thanks so much for dealing with my super basic problems here…
-Phil

[Rupert_Hoffsch1]

Hi Phil,

no worries :slightly_smiling_face: Can you please explain again what you mean by

“… two items that aren’t fixed costs but a percentage of the total…”?

I’m happy to help you further with making it possible. You’ll be able to figure out roll ups fast! Basically roll ups reference a linked records field and perform a formula on fields in those linked records. So one basic formula is called “Sum()” and basically takes a numbers field in those linked records and summarizes them. Here a link to Airtable’s support article on Rollups: https://support.airtable.com/hc/en-us/articles/360042807233-Rollup-field-overview

[Phil_G]

Hi Rupert!

Really appreciate your patience :winking_face:
Let me further explain what I mean by adding the percentage of the total sum to my table:
I basically have a long list of items/services that are fixed costs (Amount x Price = Costs).
But a couple services are 5% of the total. (For instance a commission fee)…

As it stands right now I feel like I would have to change the whole setup of my table (~90 entries) due to those 2 items that aren’t fixed costs but a percentage of the total sum.
Of course in a spreadsheet I could use a simple formula in those fields (% of SUM).
But since Airtable is a database I believe I have to create another table, link it to my first one, adding all costs there and use a rollup to pull the TOTAL back up in my first table just to be able to execute a simple calculation (percentage of SUM) in one of my rows/fields.

I stumbled about this article.

Linking every record of one table to a single record of another Show and tell

[Note: As this process is a crucial part of a number of other Airtable routines — for instance, finding the latest/first/largest/smallest something of a group of somethings, or [seemingly] any method allowing the value of a field in one record to be used in a calculation performed from a different record — I find myself entering it increasingly frequently. To save my fingers and others’ patience, I’ve decided to create a post dedicated solely to the process I can later reference as needed. The…

Do you think this is what I have to do or is there an easier way to achieve what I’m trying to do here?

Thanks so much for bearing with me :pray: .

• Phil

[kuovonne]

Welcome to the Airtable community!

Phil_G:

For instance I would like to have a field with contingency costs of 5% of the total sum.
Am I right assuming that there is no easy formula that can do that? And I would have to follow a complicated workaround with rollup fields and a linked table instead, because you can’t use summary values in formula fields?

You are correct that you cannot use summary values in formula fields. You are correct that the best way to get a sum from other records is to use linked records and a rollup field.

Phil_G:

The whole “linked-fields-and-rollups”-extravaganza :winking_face: is just a little bit over my head at the moment :disappointed_relieved: :disappointed_relieved: .

Linked records can be a bit of a mental shift for people who are used to how a spreadsheet works. However, understanding relationships between tables is a key aspect of what makes a database different from a spreadsheet. This support page has more info to help you understand linked records.

Phil_G:

It seems rather complex to me to set up 2 separate tables and link them considering I really only have two items that aren’t fixed costs but a percentage of the total

You might also have a hard time getting Airtable to present the data in the way you are used to.

Phil_G:

I stumbled about this article.
…
Do you think this is what I have to do or is there an easier way to achieve what I’m trying to do here?

I doubt that you need anything so complex.

[Phil_G]

Hi Kuovonne!

Thanks for chiming in :thumbs_up: .
I will definitely read up on your suggested support article.

kuovonne:

You might also have a hard time getting Airtable to present the data in the way you are used to.

Could you elaborate on what you mean by that? Just to make sure I don’t run into even more problems down the road;)

kuovonne:

I doubt that you need anything so complex.

Let me know if you have any idea how to tackle my problem as easily as possible… But this just gave me some hope;) Thanks!

Phil

[kuovonne]

Phil_G:

kuovonne:

You might also have a hard time getting Airtable to present the data in the way you are used to.

Could you elaborate on what you mean by that? Just to make sure I don’t run into even more problems down the road;)

I mean if you are looking for a pretty two column layout as below where all the cells are editable, you will not be able to easily get it. I’m not saying it cannot be done. I’m saying that it will not be nearly as easy as in a spreadsheet.

item 1              $ 50
item 2              $ 25
item 3              $ 25
subtotal            $100
5% of subtotal:     $  5
grand total:        $105

[Rupert_Hoffsch1 · Accepted solution]

Thanks for the explanation. Just to clarify: Those two services are 5% of which total? Are you sending out offers with certain products or which total do you mean exactly?

Because let’s say you have an “offer logic”: You would have one table for your offers. That table has a linked field to your services (if you need an individual combination of amount and price per offer, then you would create an “order line items” table which is again referencing a services table with the fixed cost per service). Back to our offer table: In this table you’re dynamically creating a sum depending on how many products you add. Now you want to add a different category of services / items, in which the commission fee is included. As this has a different logic than your other products, it should be in a different table in my opinion. So you’re linking the commission now and you create a lookup to pull the percentage amount for that commission. Last thing is now to calculate that commission by multiplying the percentage lookup with the sum from your products.

Hope this wasn’t too confusing, but just wanted to demonstrate how you could achieve this quite easily by just thinking about the data structure and separating data properly (in this case services and addons or however you want to call it).

Is that helpful for you?

[Phil_G]

Thanks so much to all of you for helping me out!
I think I just really have to familiarise myself more with the functions of Airtable to really take advantage of all the possibilities… Should be fun:)

Cheers,
Phil