Forum Discussion
Solved
Cumulative subraction in Column
Hi,
I am looking for a formula that should give me the result as per table below in last column "Cumulative remaining stock"
| Country | serial number | Item Number | Concatenate | QTY Item Number in stock | Qty Item Required for each serial number | Cumulative remining stock |
| US | 111 | a | US_a | 2 | 1 | 1 |
| US | 222 | a | US_a | 2 | 1 | 0 |
| US | 333 | a | US_a | 2 | 1 | -1 |
| US | 444 | a | US_a | 2 | 1 | -2 |
| Canada | 555 | a | Canada_a | 3 | 1 | 2 |
| Canada | 666 | a | Canada_a | 3 | 1 | 1 |
| Canada | 777 | a | Canada_a | 3 | 1 | 0 |
| Canada | 888 | a | Canada_a | 3 | 1 | -1 |
I have different serial number that are using the same item number. So I want to calculate the remaining stock considering that all of them are using the same item. In addition I should consider a filter for the country.
Can you please help?
Hi Giancarlo1977,
You could try such a calculated a column:
Here's the code in plain text for convenience:
Cumulative remaining stock = VAR CurrentSelection = [Concatenate] VAR CurrentSN = [serial number] VAR Delta = SUMX ( FILTER ( 'Table', [Concatenate] = CurrentSelection && [serial number] <= CurrentSN ), [Qty Item Required for each serial number] ) RETURN [QTY Item Number in stock] - DeltaBest Regards,
Alexander
2 Replies
Hi Giancarlo1977,
You could try such a calculated a column:
Here's the code in plain text for convenience:
Cumulative remaining stock = VAR CurrentSelection = [Concatenate] VAR CurrentSN = [serial number] VAR Delta = SUMX ( FILTER ( 'Table', [Concatenate] = CurrentSelection && [serial number] <= CurrentSN ), [Qty Item Required for each serial number] ) RETURN [QTY Item Number in stock] - DeltaBest Regards,
Alexander
