cancel
Showing results for
Did you mean:
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:

 Item Inventory Order Qty Total \$\$ Item 1 5 7 \$100 Item 2 2 7 \$100 Item 3 0 7 \$100 Item 4 100 7 \$100 Item 5 5 7 \$100 Item 6 5 7 \$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:
 Item Inventory Order Qty Total \$\$ Able to Release \$\$ Item 1 5 7 \$100 \$71.40 Item 2 2 7 \$100 \$28.60 Item 3 0 7 \$100 \$0 Item 4 100 7 \$100 \$100 Item 5 5 7 \$100 \$71.40 Item 6 5 7 \$100 \$71.40 117 42 \$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.

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## Re: Help fine tuning IF and Calculate measure

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

Established Member

## Re: Help fine tuning IF and Calculate measure

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