Conditional and selective sum with filters

Question

Hey guys! how you doing?&nbsp;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:&nbsp;The correct results for this last one would be available, out, available, out, out, available, out, out.&nbsp;The measure i am using is the "FinalCorrectStatus" you can find it in the file:&nbsp;DistributeStok&nbsp;The code is:&nbsp;FinalCorrectStatus =VAR CurrentItem = SELECTEDVALUE('DB_ORDERS'[Produto])VAR CurrentOrderNum = SELECTEDVALUE('DB_ORDERS'[Pedido])VAR StockAvailable =&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; SUM('Table Estoq'[Estoque Real]),&nbsp; &nbsp; &nbsp; &nbsp; 'Table Estoq'[Produto] = CurrentItem&nbsp; &nbsp; )//soma normal soma sem condição cumulativamenteVAR SomaNormal =&nbsp; &nbsp; SUMX(&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('DB_ORDERS'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Pedido] &lt;= CurrentOrderNum &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Produto] = CurrentItem&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Quant. Falta]&nbsp; &nbsp; )//Filtro de apoio para a SomaMaiorEstoqVAR SomaMaiorApoio =&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('DB_ORDERS'),&nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Pedido] &lt;= CurrentOrderNum &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Produto] = CurrentItem&nbsp; &nbsp; )//soma apenas quando o valor da "SomaNormal" for maior que o estoqueVAR SomaMaiorEstoq =&nbsp; &nbsp; SUMX(&nbsp; &nbsp; &nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SomaMaiorApoio,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "@Acumulado",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SomaMaiorApoio,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Pedido] &lt;= EARLIER('DB_ORDERS'[Pedido])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Quant. Falta]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; VAR TotalAcumulado = [@Acumulado]&nbsp; &nbsp; &nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(TotalAcumulado &gt; StockAvailable, 'DB_ORDERS'[Quant. Falta], 0)&nbsp; &nbsp; )//diferenca entre A soma cumulativa normal e a soma apenas dos maiores que o estoqueVAR Diferenca = &nbsp; &nbsp; SomaNormal - SomaMaiorEstoq//valor resultante de "Diferenca" + a quantidade do item no pedidoVAR Dif_mais_QTFALT = &nbsp; &nbsp; 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 estoqueVAR SomaFinal = &nbsp; &nbsp; SUMX(&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('DB_ORDERS'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Pedido] &lt;= CurrentOrderNum &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Produto] = CurrentItem &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('DB_ORDERS'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Pedido] &lt;= CurrentOrderNum &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Produto] = CurrentItem&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Quant. Falta]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) &lt;= StockAvailable ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (Diferenca + 'DB_ORDERS'[Quant. Falta]) &lt;= StockAvailable&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; 'DB_ORDERS'[Quant. Falta]&nbsp; &nbsp; )RETURN&nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; ISBLANK(StockAvailable),&nbsp; &nbsp; &nbsp; &nbsp; "out",&nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SomaFinal &lt;= StockAvailable &amp;&amp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (Dif_mais_QTFALT &lt;= StockAvailable),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "available",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "out"&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )&nbsp;its very complex, and i am almost giving up..Can anyone help me please?&nbsp;Thankyou

bhanu_gautam · Answer

ClaudioF&nbsp;, 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] &lt;= CurrentOrderNum),'DB_ORDERS'[Quant. Falta])
VAR PreviousRunningTotal =SUMX(FILTER(OrdersTable,'DB_ORDERS'[Pedido] &lt; CurrentOrderNum),'DB_ORDERS'[Quant. Falta])
RETURNIF(ISBLANK(StockAvailable),"out",IF(PreviousRunningTotal &lt; StockAvailable,IF(RunningTotal &lt;= StockAvailable,"available","out"),"out"))

claudiof · Answer

Sorry it didnt work, the result is the same;&nbsp;

v-sdhruv · Answer

Hi&nbsp;ClaudioF&nbsp;,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

v-sdhruv · Answer

Hi&nbsp;@ClaudioF&nbsp;,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

v-sdhruv · Answer

Hi&nbsp;@ClaudioF&nbsp;,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

Forum Discussion

Conditional and selective sum with filters

5 Replies

Related content

Conditional Filtering Based on Slicer Selection

Conditional Filtering

Grouped summaries with conditional filtering

Filter with conditions

Dynamic Filtering based on date selection