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

Somewhat ironically, the Airtable blurb beneath your embedded base reads

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 ‘ :white_check_mark: ’ — 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.

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!!

I’m glad it helped!

To share a base here, I click on the ‘share’ button in the upper right and then select ‘Create a shared link to the whole base.’ That will open a menu with two options; I choose the second one, ‘Private read-only link.’ That gives me a read-only link; I make sure ‘Allow viewers to copy data in this base’ is enabled, copy the link, and then paste it into the reply where I want the embedded base to appear.

This seems the most convenient way to share a low-sensitivity base (e.g., one without sensitive data or user information). The only downside is the viewer can’t access field configurations on a read-only base without making a copy of it. To get around this problem, I usually document each field using the field description and include its full configuration, including formulas.* That way the viewer can simply hover over the ‘circled-i’ description icon to see the formula.
. __________
* I say this knowing I didn’t document the fields on the base I sent you — in part because it was such a small example base, in part because the process was documented in [far too much] detail in the other links I sent… but mainly because I was lazy. :winking_face: