cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Deduct values until it turns 0 then use 0

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

1 ACCEPTED SOLUTION
Solution Sage

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)

11 REPLIES 11
Helper II

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
Solution Sage

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)

Helper II

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
Solution Sage

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)

Helper II

Hi @talespin
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]
)

Solution Sage

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

Solution Sage

Hi @Powerwoman ,

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

Helper II

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?

Super User

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Helper II

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
Super User

@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

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors