Forum Discussion

Chirantan's avatar
Chirantan
Frequent Visitor
8 years ago
Solved

Subtract rows in a Table based on a id

Hi I have a table as follows:   And I would like to achieve something like this:     I am trying to subtract rows based in the Attribute/indx, which in turn is trying to find the dif...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    Hi Chirantan,

     

    To achieve your requirement, you can create a calculate column as below:

    Calculation = 
VAR First_Value = Table1[Value]
VAR First_Index = Table1[Index]
VAR Last_Value = CALCULATE(MAX(Table1[Value]), FILTER(Table1, Table1[Index] = First_Index + 4))
RETURN
IF(Last_Value = 0, 0, Last_Value - First_Value)

     

     

    Regards,

    Jimmy Tao

Chirantan's avatar
Chirantan
Frequent Visitor
8 years ago

Hi Greg,

 

All the Attribute/Indx with the same value needs to be substracted. I am hilighting the rows to show how the calculatin is being performed.

 

 

 

I am just finding it difficult to apply DAX, as a work around I am using SQL to get the same result...but it is making the data model awkward.

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi Chirantan,

 

To achieve your requirement, you can create a calculate column as below:

Calculation = 
VAR First_Value = Table1[Value]
VAR First_Index = Table1[Index]
VAR Last_Value = CALCULATE(MAX(Table1[Value]), FILTER(Table1, Table1[Index] = First_Index + 4))
RETURN
IF(Last_Value = 0, 0, Last_Value - First_Value)

 

 

Regards,

Jimmy Tao