I feel like this should be obvious but I am not finding anything on Google that seems relevant so maybe I'm missing something. I am working with an existing dataset that was working but now isn't so I'm trying to troubleshoot. I have two tables:
Warehouse transactions, a list of all events in the warehouse such as qty adjustments due to cycle counts, picking, packing, shipping, etc.
Warehouse inventory, a list of current inventory including current costing for each product
I have a measure that looks like this:
Before Dollar Value = Transactions[item_qty] * Transactions[Products.Price Multiplier] * RELATED(Warehouse[Average])
The measure is calculating the on hand qty of the product, multiplied by a price multiplier (sometimes the pricing is multiplied by 100 or 1000 because supply chain is complicated) and then by the average cost stored in the warehouse table.
My problem is that I need to filter the table somehow to only run this calculation when the transaction type is System Cycle Count (Transaction Type is another column in the Transactions table). I feel like I've seen calculations like this before, but I can't find anything now. Am I thinking about this wrong?
I could duplicate the table in Power Query and filter it to System Cycle Count, but it's about 7 million rows so I hate to do that.
I get an error with that that says "A single value for column 'item_qty' in table 'Transactions' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."