Forum Discussion
Algorithm and iteration with Power BI / DAX
Hello,
I don't know if algorithms are possible with PowerBI.
I have several existing tables as input
'Fruits'
| Fruit | Quantity available |
| Banana | 10 |
| Orange | 7 |
| Apple | 15 |
This table contains the list of all fruits, with the quantity available and the price per unit.
'Orders'
| Order ID | Fruit | Quantity requested |
| 1 | Banana | 5 |
| 1 | Orange | 2 |
| 2 | Apple | 10 |
| 3 | Banana | 10 |
| 4 | Orange | 4 |
| 4 | Apple | 10 |
| 5 | Apple | 1 |
The algorithm should iterate each order (determine the delivery notes with the fruits to be served to each order taking into account the available fruits (Quantity delivered = min (quantity requested; quantity available), and updating the current stock = previous stock - delivered quantity).
As out I want the delivery notes table, and the stock after deliveries
Output
'Delivery note'
| Order number | Fruit | Quantity delivered | Comment |
| 1 | Banana | 5 | #Remaining Banana: 10-5 =5 |
| 1 | Orange | 2 | #Remaining Orange: 5-2 = 3 |
| 2 | Apple | 10 | #Remaining Apple: 15-10=5 |
| 3 | Banana | 5 | #Not enough stock. Delivered 5, remaining 0 |
| 4 | Orange | 4 | # Remaining 1 |
| 4 | Apple | 5 | #Remaining 0 |
| 5 | Apple | 0 | #Remaining 0 |
'Fruits after deliveries'
| Fruit | Quantity available |
| Banana | 0 |
| Orange | 1 |
| Apple | 0 |
The difficulty is to update the available quantity between each order.
5 Replies
Hi Ben75
For your question, here is the method I provided:
Note whether the correct relationship exists between the two tables.
First, you need to create two MEASURES to calculate the cumulative sum of each fruit in the order and to sort the order.
Rank = RANKX( FILTER( ALL('Orders'), 'Orders'[Fruit] = MAX('Orders'[Fruit]) ), CALCULATE(SELECTEDVALUE(Orders[Order ID])),,ASC, Dense )total orders = CALCULATE( SUM('Orders'[Quantity requested]), FILTER( ALL('Orders'), 'Orders'[Fruit] = MAX('Orders'[Fruit]) && 'Orders'[Order ID] <= MAX('Orders'[Order ID]) ) )Create measures.
Quantity available = var _fruit = SELECTEDVALUE('Fruits'[Quantity available]) var _orderTotal = CALCULATE( SUM('Orders'[Quantity requested]), FILTER( ALL('Orders'), 'Orders'[Fruit] = MAX('Orders'[Fruit]) ) ) var _QuantityAvailable = _fruit - _orderTotal RETURN IF(_QuantityAvailable > 0, _QuantityAvailable, 0)Quantity delivered = var _minRank = MINX(FILTER(ALL('Orders'), 'Orders'[Fruit] = MAX('Orders'[Fruit])), 'Orders'[Rank]) RETURN IF( SELECTEDVALUE('Fruits'[Fruit]) = SELECTEDVALUE('Orders'[Fruit]), IF( SELECTEDVALUE('Fruits'[Quantity available]) - 'Orders'[total orders] > 0, SELECTEDVALUE('Orders'[Quantity requested]), IF('Orders'[Rank] = _minRank + 1, SELECTEDVALUE('Fruits'[Quantity available]) - CALCULATE( SELECTEDVALUE('Orders'[Quantity requested]), FILTER( ALL('Orders'), 'Orders'[Fruit] = MAX('Orders'[Fruit]) && 'Orders'[Rank] = _minRank) ), 0 ) ), BLANK() )As for the COMMENT section being displayed according to your table, I'm afraid that's difficult to achieve.
Here is the result.
I hope this helps you.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for you help.
What is doing exactly the Rank measure?
Could you please elaborate on:
'Orders'[Fruit] = MAX('Orders'[Fruit])Also, how can I get the result in a table?
Hi bg75
Sort your orders by grouping them according to fruits. The purpose is to define the order of orders and help to determine the inventory.
Click Visual and select the fields you need to create a table.
Thank you 🙏
How to proceed if we want to take the order into account only if all the different fruits are available?
Hi,
Why isn't there a date column in both inut tables?
