Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Thanks in advance!
Solved! Go to Solution.
You should look at SUMX (there are other ...X formula too):
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |