Deduct values until it turns 0 then use 0

[Powerwoman]

Dear friends,
I am working on a measure called 'remainingQuanity' that deducts the [Quantity] from [Inventory Units].

Sample table:

Index	remainingQuantity	inventoryUnits	Quantity
82078	3	4	1
82079	4	4	-1
82080	3	4	1
82081	2	4	1
82082	1	4	1
82083	0	4	1
82084	-1	4	1
82085	-2	4	1
82086	-3	4	1
82087	-4	4	1

Once the [remainingUnits] becomes 0 it should use 0 instead of the calculation:

It's sound so easy, but what's the right dax?

Thank you

[Powerwoman]

ok, update: 
I need this calculation to be done by item no.
Is that possible?

Index	Item_No	inventoryUnits	Quantity
82078	048454	4	1
82079	048454	4	-1
82080	048454	4	1
82081	048454	4	1
82082	048454	4	1
82083	048454	4	1
82084	048454	4	1
82085	048454	4	1
82086	048454	4	1
82087	048454	4	1
82088	999999	4	1
82089	999999	4	-1
82090	999999	4	1
82091	999999	4	1
82092	999999	4	1
82093	999999	4	1
82094	999999	4	1
82095	999999	4	1
82096	999999	4	1
82097	999999	4	1

[talespin]

Hi @Powerwoman 

Another method, as calculated column

Column =

VAR _Index = TestTbl1[Index]

VAR _inventoryUnits = TestTbl1[inventoryUnits]

VAR _SumQty = CALCULATE(SUM(TestTbl1[Quantity]), REMOVEFILTERS(TestTbl1), TestTbl1[Index] <= _Index)

VAR _result = _inventoryUnits - _SumQty

RETURN IF( _result > 0, _result, 0)

[Powerwoman]

Hi @talespin,
this works fine, thank you.

Can it be done by item no as well?

Index	Item_No	inventoryUnits	Quantity
82078	048454	4	1
82079	048454	4	-1
82080	048454	4	1
82081	048454	4	1
82082	048454	4	1
82083	048454	4	1
82084	048454	4	1
82085	048454	4	1
82086	048454	4	1
82087	048454	4	1
82088	999999	4	1
82089	999999	4	-1
82090	999999	4	1
82091	999999	4	1
82092	999999	4	1
82093	999999	4	1
82094	999999	4	1
82095	999999	4	1
82096	999999	4	1
82097	999999	4	1

[talespin · Accepted solution]

Hi @Powerwoman ,

Column =

VAR _Index = TestTbl1[Index]

VAR _inventoryUnits = TestTbl1[inventoryUnits]

VAR _ItemNo = TestTbl1[Item_No]

VAR _SumQty = CALCULATE(SUM(TestTbl1[Quantity]), REMOVEFILTERS(TestTbl1), TestTbl1[Index] <= _Index, TestTbl1[Item_No] = _ItemNo)

VAR _result = _inventoryUnits - _SumQty

RETURN IF( _result > 0, _result, 0)

[Powerwoman]

Hi @talespin 
Thanks for your solution.
The only 'bad' thing was that it killed my Power BI desktop because the original table had 90.000 rows.

My solution to this:

Different calculation of the 'running Total'

var _runningTotalQuantity =
sumx(
FILTER(
all(table),
table[Item_No] = EARLIER(table[Item_No]) &&
table[Index] <= EARLIER(table[Index])
),
table[Quantity]
)

[talespin]

hi @Powerwoman 

90,000 shouldn't be a problem, if possible please share pbix file, removing any sensitive data.

[talespin]

Hi @Powerwoman ,

Yes, But I need something to order, which attribute?

[Powerwoman]

Hi @talespin ,
great! That would be item no & index.

The table is already sorted this way.
Can it be grouped by item no and sorted by index?

[ThxAlot]

[Powerwoman]

Hi @ThxAlot ,
it seems like this is working.
Can it be done by item no as well?

Index	Item_No	inventoryUnits	Quantity
82078	048454	4	1
82079	048454	4	-1
82080	048454	4	1
82081	048454	4	1
82082	048454	4	1
82083	048454	4	1
82084	048454	4	1
82085	048454	4	1
82086	048454	4	1
82087	048454	4	1
82088	999999	4	1
82089	999999	4	-1
82090	999999	4	1
82091	999999	4	1
82092	999999	4	1
82093	999999	4	1
82094	999999	4	1
82095	999999	4	1
82096	999999	4	1
82097	999999	4	1

[bhanu_gautam]

@Powerwoman , Try using below method

RemainingQuantity =
CALCULATE(
SUMX(
FILTER(
ALL('YourTable'),
'YourTable'[Index] <= MAX('YourTable'[Index])
),
'YourTable'[Quantity]
),
FILTER(
ALL('YourTable'),
'YourTable'[Index] <= MAX('YourTable'[Index])
)
)

Please accept as solution and give kudos if it helps