Complex conditional summing

[ClaudioF]

Hey everyone! how you doing?

I have been now a month trying to make this logic work in dax but no solution.

Cna anyone please help me with this? or even just say that power Bi will not be able of doing it happen?

Here is the thing: I need to distribute evenly the stock of an item throught the orders of this item, in a way that the result is as shown in the table bellow: 

I need the stock to be distributed in a way that when its not enought to the order it will try to fit in the other order until the total stock is used. Use the stock amount of 21 for this example, you will see that the ones set as available, when summed will be exactly 21.

The status column is the outcome that i need from the dax code.

This is very hard, i don't even know if its possible; can anyone please helpe me with it?

Thankyou a lot allready.

[v-kpoloju-msft]

Hi @ClaudioF,

Thank you for reaching out to the Microsoft fabric community forum. Thank you @techies, for your inputs on this issue.

You're certainly not alone in finding sequential stock allocation logic challenging in DAX, as it is not inherently procedural.

The positive news is that what you're aiming to achieve is feasible in Power BI using a calculated column. Here's the approach that works:

1. Create a running total of the QtyRequested per order, sorted by OrderID.
2. Compare this cumulative quantity to your total available stock (e.g., 21 units).
3. If the cumulative total is less than or equal to the stock, mark the order as "Available"; otherwise, mark it as "Not Available".

Here’s a simplified version of the DAX formula you can use for a calculated column:

Status =

VAR Stock = 21

VAR CumQty =

    CALCULATE (

        SUM ( Orders[QtyRequested] ),

        FILTER (

            Orders,

            Orders[OrderID] <= EARLIER ( Orders[OrderID] )

        )

    )

RETURN

    IF ( CumQty <= Stock, "Available", "Not Available" )

Just make sure your table has a proper OrderID or sort column to maintain row order, and you can adjust the stock reference if it comes from another table.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @ClaudioF,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @ClaudioF,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @ClaudioF,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[techies]

Hi @ClaudioF please try this, add as a calculated column

result column = VAR StockLimit = 21

var CurrentRow = 'Sheet3'[Quant. Falta]

VAR RunningTotal =

    SUMX(

        FILTER(

            ALL('Sheet3'),

            'Sheet3'[Produto] = EARLIER('Sheet3'[Produto]) &&

            'Sheet3'[Quant. Falta] <= CurrentRow

        ),

        'Sheet3'[Quant. Falta]

    )

RETURN

IF(RunningTotal <= StockLimit, "available", "out")

[ClaudioF]

Hi @techies , I can't do as calculated column because I need it to be donamic, when I filter by date for example, it will do the same logic but only with the filtered orders in that visual..

[techies]

ok , try this

ResultMeasure =

VAR StockLimit = 21

VAR CurrentRow = SELECTEDVALUE('Sheet3'[Quant. Falta])

VAR RunningTotal =

    SUMX(

        FILTER(

            ALLSELECTED('Sheet3'),

            'Sheet3'[Produto] = SELECTEDVALUE('Sheet3'[Produto]) &&

            'Sheet3'[Quant. Falta] <= CurrentRow

        ),

        'Sheet3'[Quant. Falta]

    )

RETURN

IF(RunningTotal <= StockLimit, "available", "out")

[ClaudioF]

im sorry for the missing information, but the trick of this all is also that this might be distribute to the earliest order then tto the oldest, each of those lines is a order with number, 111, 222, 333, 444, 555, 666, 777, 888, in order...

[techies]

Hi @ClaudioF as i understand,  

resultmeasure = VAR StockLimit = 21

VAR CurrentOrder = SELECTEDVALUE('Sheet3'[order number])

VAR RunningTotal =

    SUMX(

        FILTER(

            ALLSELECTED('Sheet3'),

            'Sheet3'[Produto] = SELECTEDVALUE('Sheet3'[Produto]) &&

            'Sheet3'[order number] <= CurrentOrder

        ),

        'Sheet3'[Quant. Falta]

    )

RETURN

IF(StockLimit - RunningTotal >= 0, "available", "out")

Hope it works, please share pbix file if possible

[ClaudioF]

Hey there! i just got an update to this problem, look the post: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Conditional-and-selective-sum-with-f... 

its an other post i did with more explanig about the problem and the file..

Thankyou