sum based on column value

[aminsaleh]

Hi, I have a table called stock which contains all stocks

Inside it there is a column called Available which calculates the available stock now.

>> In the stock table there is an inline table called (Add a stock), and I use this inline table to add new qty to a specific sku item.

>> In the (add a stock) table I have a column called confirm of type enum and one vale yes. and the idea of the confirm column is to confirm the new qty once added.

------------

In the available column in the stock table I use this formula to compute the available qty for an sku item.

If(
CONTAINS(Add stock ca[Confirm], "Yes"),
SUM(SELECT(Add Stock CA[Qty],[SKU]=[_THISROW].[SKU]))+[Returned]-[Sold]-SUM(SELECT(Add Stock CA[Factory Defects],[SKU]=[_THISROW].[SKU])),
0
)

Now the problem is for example If I have an item called (Computer office chair)

If I open that item and click on (add a stock) and add for example 10 unit and click confirm... the available will increase by 10 which is true.

But,  If I do the same and do not click confirm, it is also added!!

I tried _thisrow and did not work.

Any hint plz

[TeeSee1 · Accepted solution]

In your expression

If(
 CONTAINS(Add stock ca[Confirm], "Yes"),
 SUM(SELECT(Add Stock CA[Qty],[SKU]=[_THISROW].[SKU]))+[Returned]-[Sold]-SUM(SELECT(Add Stock CA[Factory Defects],[SKU]=[_THISROW].[SKU])),
0
)

CONTAINS(Add stock ca[Confirm], "Yes") is always TRUE because all rows in Add stock CA are searched. That is why whatever entries you make, regardless of the value of [confirm] , are added to [available]

Try instead.

SUM(
 SELECT(Add Stock CA[Qty],AND([CONFIRM]="Yes",[SKU]=[_THISROW].[SKU]))
)
+ [Returned]
- [Sold]
- SUM(
 SELECT(Add Stock CA[Factory Defects],[SKU]=[_THISROW].[SKU])
)

 (note: this assumes that [Confirm] is of text type)

[aminsaleh]

@TeeSee1 

Very much appreciated