Hey @Himanshu_Godara , welcome to the Community!
You’re modelling your data wrong. Unlike Excel, in Coda rows and columns have strictly specific meanings: rows are records and columns are properties. Each column describes a measure or a dimension.
A measure is a value of a record:
- Number of sales
- Number of items available
- Restock price etc.
and dimensions are different “groupings” of such values that belong on different planes:
-
Warehouses and Products are different dimensions. “Cola”, “Fiji Water”, and “Gatorade” are values along the dimension of Products, and “Warehouse 1” is a value along the dimension of Warehouses.
-
Manufacturers and Products are on the same dimension, just a different depth of detail. Both “Cola”, “Sprite”, and “Fanta” Products belong to a more general group of “The Coca Cola Company” value on Manufacturers level. But since each smaller group is uniquely linked to a larger one, the latter can be inferred from the former through a lookup link. Hence it’s not a separate dimension.
Then, a row (a record) contains information about a value on each dimension and a measure that describes the state of things at the intersection of that measures. It can have multiple columns per dimension to contain values from different levels of detail along that dimension (e.g. have both Manufacturer and Brand columns) but very often only the most specific one will be an input, and the more aggregate ones will be lookup formulas.
Now take a look at your Inventory table. You implemented it in such way that tells:
- Warehouse name is a dimension (correct). There can be zero, one, or more Inventory records for each Warehouse value.
- XP100 is a measure — a property of an Inventory row
- XP200 is also a measure, a different property of an Inventory row
and so on.
This is incorrect because XP100, XP200 etc are not properties (not separate measures or separate dimensions) — they are values at a common dimension, the dimension of Products. So to model this properly, your Inventory table must look like this:
Then your formula for X will be as simple as
Inventory.Filter(
Warehouse = [Select warehouse]
AND Product = [Select product]
).Units.Sum()
(sum just in case there’s two rows for the intersection of the same Warehouse and Product; Filter always return a list of rows anyway)
And if you want to output the data in a “pivot table” way, you can make a view of this table and use left and top grouping
I’ll teach all of these things in my upcoming Coda Fundamentals course 