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.
