So i am currently facing a problem which I really dont know how to solve:
I am creating an availability report, that contains articles from two Warehouses and we are showing the "Open to sell Value" which is calculated like this:
Open to Sell = SUM('Stock'[Stock Quantity CB])-SUM('Stock'[Backorder Quantity])
But unfortunately our data is not a 100% correct or usable because:
If there are two articles (with the same article ID) that are in Warehouse 1 AND Warehouse 2 the backorder QTY is the same... therefore the backorder QTY is way too high because everytime there are two warehouses for one article the qty is double.. But its not always the case that articles are in both warehouses, there are many article that are only in 1 OR 2.
I need to calculate open to sell like above but as soon as there are Warehouse 1 and 2 for one article it should only take one value.
I wanted to try something likes this Open to Sell Ghost = IF('Ghost_Stock'[Warehouse Code] = 'EM' AND 'EMBI', SUM('Ghost Stock'[Stock Quantity CO])-CALCULATE(SUM('Ghost Stock'[Backorder Quantity]), 'Ghost Stock'[Warehouse Code] = 'EM'), [SUM('Ghost Stock'[Stock Quantity CO])-SUM('Ghost Stock'[Backorder Quantity])]) but of course it doesnt work because the two warehouses are not in one row it looks like this:
I hope I made it clear what the issue is. Does anybody know what to do? Its not a possibility to change the data..