Subtract Between Rows with Same ID

[Anonymous]

Hi Team,

I want to subtract rows with the same Id. Example is what i'm highlighting. Some TaskIDs are unique, some don't.

Unique TaskID: We don't do anything.

NOT Unique TaskId, i want to subtract "TaskBaselineDuration" of BaselineNumber = 0 for BaselineNumber = 1 (they have the same TaskId). Basically i want to know the difference between Baseline 1 and Baseline 0 if it has Baseline. 

Desired Result is: TaskBaselineDuration for IDs highlighting: (2064-4529)=-2465 or (1855-8)= 1847

i read many similar posts but couldn't find solution. Thanks

[v-henryk-mstf]

Hi @Anonymous ,

First of all, pay attention to your yellow mark is wrong. According to your description, you want to get the value of 1,0 corresponding to TaskBaselineDuration column corresponding to row subtraction under the same TaskID column. I did the following test to create the Subtract_ Result measure: if the ID is the same and the BaselineNumber is 1,0, then the corresponding rows in the TaskBaselineDuration column will be subtracted, otherwise the value will not change.

Here is the sample pbix file.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@Anonymous 

Not sure if one ID will have only one baseline 1 or 0. if so ,you can try to create a new column.

Column = MAXX(FILTER('Table1',Table1[Task ID]=EARLIER(Table1[Task ID])&&Table1[baseline]=0),Table1[TaskBaselineDuration])-Table1[TaskBaselineDuration]

[Anonymous]

Thank you so much. Your instructions are awesome and work for my case. 

[StefanoGrimaldi]

I assume you want to subtract the values of ID that appear more tham once? from the total value of the column? lets say total value column minus those 4 highlighted values? or minus only those that have a value of 1 in baseline?

if its the second case this simply measure will do: 

Measure = SUM('Table'[value])-CALCULATE(SUM('Table'[value]),'Table'[baseline]<>"0") this should actually work for both cases. (my example measure have baseline as text, if its a number delete the "")