Algorithm and iteration with Power BI / DAX

[Ben75]

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.

[Ashish_Mathur]

Hi,

Why isn't there a date column in both inut tables?

[Anonymous]

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.

[Ben75]

Thank you 🙏

How to proceed if we want to take the order into account only if all the different fruits are available?

[bg75]

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?

[Anonymous]

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.