Forum Discussion

Zabeer's avatar
Zabeer
Icon for Helper I rankHelper I
6 years ago

Subtracting rows based on another column category

Hello, 

I would like to create a new column that would subtract the row values in 'FH' based on the 'Product'. The sample data set and corresponding expected output is as shown below. Kindly help.
Thanks.

ProductFHExpected Outcome
A20000
A50003000
A90004000
B25000
B60003500
B100004000

 

Need Help

3 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Zabeer 

     

    Try this

     

    Output = 
var _CurrentProduct=NewTable[Product]
var _PreviousFH= MAXX(FILTER(NewTable,NewTable[FH]<EARLIER(NewTable[FH]) && NewTable[Product]=_CurrentProduct),NewTable[FH])
var _result=NewTable[FH]-_PreviousFH
return IF(ISBLANK(_PreviousFH),0,_result)

     



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Zabeer 

    try a column

    Expected Outcome = 
var _prevVal = CALCULATE(MAX('Table'[FH]),FILTER('Table','Table'[Product]=EARLIER('Table'[Product]) && 'Table'[FH] < EARLIER('Table'[FH])))
RETURN
IF(ISBLANK(_prevVal), 0, _prevVal)
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Zabeer , Try like a new column

    Expected = [FH] - maxx(filter(table,[Product] = earlier([Product]) && [FH] < earlier([FH])),[FH])

     

    Ideally, there should date to do this