Issue with double values

[MarlenePirker]

Hello! 

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: 

Article ID	Warehouse	Backorder QTY
12345	W-1	700
12345	W-2	700

I hope I made it clear what the issue is. Does anybody know what to do? Its not a possibility to change the data.. 

Thank you!

Best regards, 

Marlene

[NikhilChenna]

Hi @MarlenePirker ,

I think the below is the solution.

1. Create a rank calculated column "Rankc" based on the Article column using Rankeq function.

2. After this create another caculated column as  = 
Open to Sell = IF('table1'[Rankc]=1, SUM('Stock'[Stock Quantity CB])-SUM('Stock'[Backorder Quantity]),BLANK())

Check if it works.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[MarlenePirker]

Hello! 

Thank you for your reply. So what column do i need to put into rank.eq? I am not sure what it does? 

Rankc = RANK.EQ(0, 'Stock'[Articlenumber]) - this gives me an error message that I cant compare text and integer - i am not sure what this means. 
Articlenumber column is text. 

Can you help me with this? I have never used rank eq before 😄

Thank you !

Best regards, 
Marlene