cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarlenePirker
Frequent Visitor

Issue with double values

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 IDWarehouseBackorder QTY
12345W-1700
12345W-2700

 

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

2 REPLIES 2
NikhilChenna
Responsive Resident
Responsive Resident

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!

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.