Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarlenePirker
Helper I
Helper I

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
Continued Contributor
Continued Contributor

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.