Determine Market Share as a percentage of Total Sales in another field

#1

Hello,
Does anyone now how can i achieve this:
I have a field with a few records that give me total sales and i want to be able to automatically update another field with the market share percentage, but i have not been able to come up with a formula that takes the amount from a record from a field and divides the sum from that field
Formula should be: SUM(Total)/Total and that would give me a % but its not working
In the linked table I can put the total sales manually in the formula: 134,000/Total and it works, but i want this to be automatic, i dont want to modify the formula every time sales change
Any help would be appreciated

#2

Airtable works like a spreadsheet but gives you the power of a database to organize anything

â because in some ways Airtable doesnât work like a spreadsheet. Youâve just found one of them.

Elsewhere in this forum, I wax philosophical about Airtable and the spreadsheet metaphor. The TL;DR version of the linked post is âDatabase that works like a spreadsheetâ might be an excellent elevator pitch for the service, bt it can also cause confusion. One way this often happens is it leads people to believe row order has an intrinsic meaning in Airtable (it doesnât); another is people expect to be able to define functions that address the same field across multiple records â for instance, your perfectly logical attempt to specify `SUM(Total)`.

As best I can tell, the only way to accomplish what you want to do is through the use of a second table. This new table will contain only a single row, but every record in your main table will link to it.* This second tale exists for one reason: To roll up `{Total}` from the main table with the aggregation function `SUM(values)`. This value is then passed back to the main table via a lookup or rollup and used to calculate market share. (BTW, there seems to be a problem with the formula in the base you referenced.)

The downside, of course, is that you have to link every record to the one in the `[calc]` table. Typically when I need to do that, I give the connected-to record a name something like ââ â the White Heavy Check Mark emoji â so the link serves as visual confirmation Iâve made the connection. Establishing the link during entry of a new record takes two mouse clicks: One on the plus sign in the link field, which causes a drill-through to the `[calc]` table, and the second on the record itself.

Iâve put together a quick example base as a demonstration. (Note it uses a rollup to return the value of `{SUM(Total)}` from the `[calc]` table to the `[accounts]` table, with the market share calculation embedded in the rollupâs aggregation function. While this does not appear to be documented anywhere, such complex aggregation functions appear to be supported throughout Airtable.)
. __________

• It is incredibly easy to add such a linked record to an existing base: Essentially, after creating the new table and its one row, add a text field to the existing table. Copy/paste the name of the `[calc]` record into the new text field for every existing record, and then change the field type from âSingle Line Textâ to âLinked Record.â The process is described in more detail in the first three bullet points (including the sub-bullets under the third item) given in this reply.

#3

Thanks for all your input, greatly appreciated and thanks for pointing out the error in my formula, i had it backwards and didnt even notice when I took the screen shot.
How do you share a base like you did?
Well thank you very much, i will use your solution to solve my problem!!

#4