I’m using AirTable to describe all products my company sells, or has ever sold, in our shop. Each record is a product. The tricky part is that some products are technically product bundles. To help describe this, I have a field called “Bundled Products” which can link to other records, for example:
Product X (bundle)
Bundled Products: Product Y, Product Z
Here’s where it gets tricky. The way our store reports revenue, every product (including bundles) has sales recorded separately. But for analysis purposes, I want to be able to consolidate the revenue from Product Y with the relative percent of revenue from Product X, the bundle that includes it.
I’m struggling to figure out how to create a formula or rollup for this. What I’ve done so far is define an additional field called “Part of Bundles”, so Product Y now has a reference to Product X, that contains it.
If I were writing a program for this, here are the logical steps I would need to calculate this “Master Revenue” figure for Product Y.
FOR EACH record in “Part of Bundles”:
Divide Product Y’s MSRP by (Bundle)'s MSRP to calculate what % of the bundle Product Y is. For the sake of example, let’s say Product Y is 60% of Product X.
Multiply the % calculated in step 1 by (Bundle)'s Net Revenue, to find the relative sales for this volume.
Repeat for all records in (Part of Bundles).
Once done, I would add THAT number to Product Y’s net revenue, so find the total amount.
In programmer logic this is super simple but it’s not apparent how to do this with AirTable. Any help appreciated… thanks in advance!