topic Re: Conditional and selective sum with filters in DAX Commands and Tips

Conditional and selective sum with filters

ClaudioF — Fri, 14 Mar 2025 02:40:54 GMT

Hey guys! how you doing?

Just got an insight in my dax code, i've been working on the logic of it for a while and i think im getting somewhere, but still need help. I need the code to return the "out" and "available" status correctly for each order, in a way that it will always start with the earliest order then to the oldest. the reult i got for this actual data is right, but it changes when i change the values, getting wrong results, here is an example of the right result i got.:

I have used many calculations and contitions to achieving this result, but the logic is still not complete.

look at the result i get when change the values:

The correct results for this last one would be available, out, available, out, out, available, out, out.

The measure i am using is the "FinalCorrectStatus" you can find it in the file: DistributeStok

The code is:

FinalCorrectStatus =

VAR CurrentItem = SELECTEDVALUE('DB_ORDERS'[Produto])

VAR CurrentOrderNum = SELECTEDVALUE('DB_ORDERS'[Pedido])

VAR StockAvailable =

CALCULATE(

SUM('Table Estoq'[Estoque Real]),

'Table Estoq'[Produto] = CurrentItem

)

//soma normal soma sem condição cumulativamente

VAR SomaNormal =

SUMX(

FILTER(

ALLSELECTED('DB_ORDERS'),

'DB_ORDERS'[Pedido] <= CurrentOrderNum &&

'DB_ORDERS'[Produto] = CurrentItem

'DB_ORDERS'[Quant. Falta]

)

//Filtro de apoio para a SomaMaiorEstoq

VAR SomaMaiorApoio =

FILTER(

ALLSELECTED('DB_ORDERS'),

'DB_ORDERS'[Pedido] <= CurrentOrderNum &&

'DB_ORDERS'[Produto] = CurrentItem

)

//soma apenas quando o valor da "SomaNormal" for maior que o estoque

VAR SomaMaiorEstoq =

SUMX(

ADDCOLUMNS(

SomaMaiorApoio,

"@Acumulado",

SUMX(

FILTER(

SomaMaiorApoio,

'DB_ORDERS'[Pedido] <= EARLIER('DB_ORDERS'[Pedido])

'DB_ORDERS'[Quant. Falta]

)

VAR TotalAcumulado = [@Acumulado]

RETURN

IF(TotalAcumulado > StockAvailable, 'DB_ORDERS'[Quant. Falta], 0)

)

//diferenca entre A soma cumulativa normal e a soma apenas dos maiores que o estoque

VAR Diferenca =

SomaNormal - SomaMaiorEstoq

//valor resultante de "Diferenca" + a quantidade do item no pedido

VAR Dif_mais_QTFALT =

Diferenca + SELECTEDVALUE('DB_ORDERS'[Quant. Falta])

//verifica se o estoque já atingiu seu limite, vai somando o que estiver dentro da condição de caber no estoque

VAR SomaFinal =

SUMX(

FILTER(

ALLSELECTED('DB_ORDERS'),

'DB_ORDERS'[Pedido] <= CurrentOrderNum &&

'DB_ORDERS'[Produto] = CurrentItem &&

(

SUMX(

FILTER(

ALLSELECTED('DB_ORDERS'),

'DB_ORDERS'[Pedido] <= CurrentOrderNum &&

'DB_ORDERS'[Produto] = CurrentItem

'DB_ORDERS'[Quant. Falta]

) <= StockAvailable ||

(Diferenca + 'DB_ORDERS'[Quant. Falta]) <= StockAvailable

)

'DB_ORDERS'[Quant. Falta]

)

RETURN

IF(

ISBLANK(StockAvailable),

"out",

IF(

SomaFinal <= StockAvailable &&

(Dif_mais_QTFALT <= StockAvailable),

"available",

"out"

)

its very complex, and i am almost giving up..

Can anyone help me please?

Thankyou

Re: Conditional and selective sum with filters

bhanu_gautam — Fri, 14 Mar 2025 08:12:51 GMT

ClaudioF , Try using

FinalCorrectStatus =
VAR CurrentItem = SELECTEDVALUE('DB_ORDERS'[Produto])
VAR CurrentOrderNum = SELECTEDVALUE('DB_ORDERS'[Pedido])

VAR StockAvailable =
CALCULATE(
SUM('Table Estoq'[Estoque Real]),
'Table Estoq'[Produto] = CurrentItem
)

VAR OrdersTable =
FILTER(
ALLSELECTED('DB_ORDERS'),
'DB_ORDERS'[Produto] = CurrentItem
)

VAR RunningTotal =
SUMX(
FILTER(
OrdersTable,
'DB_ORDERS'[Pedido] <= CurrentOrderNum
),
'DB_ORDERS'[Quant. Falta]
)

VAR PreviousRunningTotal =
SUMX(
FILTER(
OrdersTable,
'DB_ORDERS'[Pedido] < CurrentOrderNum
),
'DB_ORDERS'[Quant. Falta]
)

RETURN
IF(
ISBLANK(StockAvailable),
"out",
IF(
PreviousRunningTotal < StockAvailable,
IF(
RunningTotal <= StockAvailable,
"available",
"out"
),
"out"
)
)

Re: Conditional and selective sum with filters

ClaudioF — Fri, 14 Mar 2025 16:22:47 GMT

Sorry it didnt work, the result is the same;

Re: Conditional and selective sum with filters

v-sdhruv — Thu, 15 May 2025 15:17:09 GMT

Hi ClaudioF ,
Just wanted to follow up if you were able to resolve the issue or you need any assistance?
If you were able to resolve the issue, can you post your solution so that it would benefit other members to reach the solution easily.
Thank You

Re: Conditional and selective sum with filters

v-sdhruv — Fri, 23 May 2025 12:36:18 GMT

Hi @ClaudioF ,
Just wanted to follow up if you were able to resolve the issue or you need any assistance?
If you were able to resolve the issue, can you post your solution so that it would benefit other members to reach the solution easily.
Thank You

Re: Conditional and selective sum with filters

v-sdhruv — Fri, 20 Jun 2025 11:46:48 GMT