Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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):
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |