One option would be to change the calculation to the following:
ZN(sum([Inventory Qty])) + ZN(SUM([Sheet1 (TomatoTransfers.xlsx)].[Incoming Qty])) - ZN(SUM([Sheet1 (TomatoOrders.xlsx)].[Sales Qty]))
Hope this helps!
I've run into similar problems in the past. This is probably because you are using one table as the primary table or the table that the other sheets are being left joined to. A work around is to create dummy 'volume' records from the other sheets and insert them into your primary data source. This makes the underlying data look ugly, but helps fill in the missing pieces.
I’ll give it a try
Hi Tracy- didn’t think of ZN, I’ll see if works