Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Zabeer
Helper I
Helper I

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

 

3 REPLIES 3
nandukrishnavs
Super User
Super User

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

 

Capture.JPG



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


Regards,
Nandu Krishna

amitchandak
Super User
Super User

@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

az38
Community Champion
Community Champion

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)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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