Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Subtract Between Rows with Same ID

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

 

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    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]

     

4 Replies

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Resident Rockstar
    5 years ago

    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 "")
  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    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.