cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
STS_Joshua Regular Visitor
Regular Visitor

Help fine tuning IF and Calculate measure

Hi all, 

 

I'm looking for some help with a measure. I have a table that is showing a bunch of items on back order and would like to know how much of those items we can ship and how much money this would make. The table is built like so:

 

ItemInventoryOrder QtyTotal $$
Item 157$100
Item 227$100
Item 307$100
Item 41007$100
Item 557$100
Item 657$100

 

I have a measure that evaluates the quantities and if the ordered quanity is larger than the inventory, it will multiply the total order by that ratio, otherwise it will give the total amount:

ATShipqty = IF('OOBO'[openqtysum]>'OOBO'[onhandsum], ('OOBO'[onhandsum]/'OOBO'[openqtysum])*'OOBO'[BO$$], 'OOBO'[BO$$])
This gives me the table that I want:
ItemInventoryOrder QtyTotal $$Able to Release $$
Item 157$100$71.40
Item 227$100$28.60
Item 307$100$0
Item 41007$100$100
Item 557$100$71.40
Item 657$100$71.40
 11742$600$600

 

The problem is the end total because the total inventory is greater than the total order quantity so my measure will then just spit back the total order price. I need my measure to give me a sum total of the calculated available to release price, in this case $342.

 

Thanks in advance!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Aron_Moore Established Member
Established Member

Re: Help fine tuning IF and Calculate measure

You should look at SUMX (there are other ...X formula too):

 

Capture.PNG

1 REPLY 1
Aron_Moore Established Member
Established Member

Re: Help fine tuning IF and Calculate measure

You should look at SUMX (there are other ...X formula too):

 

Capture.PNG