Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Powerwoman
Helper II
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:

IndexremainingQuantityinventoryUnitsQuantity
82078341
8207944-1
82080341
82081241
82082141
82083041
82084-141
82085-241
82086-341
82087-44

1

 

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

Powerwoman_0-1706797304204.png

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


Thank you

 

1 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)
 

talespin_0-1706811367354.png

 

View solution in original post

11 REPLIES 11
Powerwoman
Helper II
Helper II

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

IndexItem_NoinventoryUnitsQuantity
8207804845441
820790484544-1
8208004845441
8208104845441
8208204845441
8208304845441
8208404845441
8208504845441
8208604845441
8208704845441
8208899999941
820899999994-1
8209099999941
8209199999941
8209299999941
8209399999941
8209499999941
8209599999941
8209699999941
8209799999941
talespin
Solution Sage
Solution Sage

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)
 

talespin_0-1706804308031.png

 

Hi @talespin,
this works fine, thank you.

Can it be done by item no as well?

IndexItem_NoinventoryUnitsQuantity
8207804845441
820790484544-1
8208004845441
8208104845441
8208204845441
8208304845441
8208404845441
8208504845441
8208604845441
8208704845441
8208899999941
820899999994-1
8209099999941
8209199999941
8209299999941
8209399999941
8209499999941
8209599999941
8209699999941
8209799999941

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)
 

talespin_0-1706811367354.png

 

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]
)

hi @Powerwoman 

 

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

Hi @Powerwoman ,

 

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

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

ThxAlot_0-1706801827103.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



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

IndexItem_NoinventoryUnitsQuantity
8207804845441
820790484544-1
8208004845441
8208104845441
8208204845441
8208304845441
8208404845441
8208504845441
8208604845441
8208704845441
8208899999941
820899999994-1
8209099999941
8209199999941
8209299999941
8209399999941
8209499999941
8209599999941
8209699999941
8209799999941
bhanu_gautam
Super User
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!





Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.