Hi, we are triying to use Analytics to visuallize an inventory in Zoho Inventory. Our main table is Inventory Mapping, and the columns we are interested in are "Product ID" (item's ID), Inventory Lot ID and Quantity On Hand.

We know that if we group the data as Product ID->Inventory Lot ID->Quantity On Hand, we get the different quantities on hand and their sum is the same quantity in stock that Zoho Inventory gives to us. In the given picture you can see what we are talking about: the column with the aggregated formula should be 189, not 267.

We know that inside the table, Quantity On Hand has repeated values by lot and the aggregated sum is applied to all those values. For example, if we have the pairs Lot ID-Quantity On Hand:

1234 45

1234 45

1234 45

1235 0

1236 20

the aggregated value will be 155 (45+45+45+0+20) but the real value (the one we want to calculate) is 65 (45+0+20).

So, how can we sum only the grouped values and not the whole column?