Forum Discussion

Lilia's avatar
Lilia
Helper I
9 years ago
Solved

Calculating difference between consecutive rows

Calculating difference between ROWS   Hello, Could you please help me with advice how I could calculate the value difference between two rows? In the table below:   ProblemID ProblemFiel...
Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

    Probably a pivot-operations is what you're looking for:

    Check column "ProblemFieldID" -> Transform -> Pivot Column -> Choose "NumVal" in Value-Field.

     

    This will return a table with 1 additional column per ProblemFieldID which you then can build your calculations on.

Lilia's avatar
Lilia
Helper I
9 years ago

Greg_Deckler The rows could be not in the same order  / might be 10 previous or 6 previous/.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

OK, so then is the pattern that you always want to match problem ID's 4 and 7 or how do you know which earlier row to use?

  • Lilia's avatar
    Lilia
    Helper I
    9 years ago

    Greg_Deckler

    In the table, in column Problem ID every problem has 2 problemFieldID / one that show problem filed id 4 and one with problem field 7/

     

    What I'm trying to calculate is

    Problem id 6A6C4C2A-7328-4A91-B598-6F629C905A86  temprature =  NumVal/Problem Field Id=4/ - (Problem id 4 - problem ID 7).

     

     

    ProblemID

    ProblemFieldID

    NumVal

    Result

    Temperature

    6A6C4C2A-7328-4A91-B598-6F629C905A86

    4

    98

    text =(Problem id 4 - problem ID 7)

    10

    6A6C4C2A-7328-4A91-B598-6F629C905A86

    7

    88

     

     

    C8E745BC-7CC2-4220-97DC-503664FF5828

    4

    230

    text =(Problem id 4 - problem ID 7)

    43

    C8E745BC-7CC2-4220-97DC-503664FF5828

    7

    187

     

     

    D814D0FF-3B18-4C51-990D-F6FF4CAE15F1

    4

    93

    text =(Problem id 4 - problem ID 7)

    10

    D814D0FF-3B18-4C51-990D-F6FF4CAE15F1

    7

    83

     

     

     

     

    I was thinking that if the table is transpose:

     

    Problem

    ProblemID                                                                Comp T/ ProblemfieldID 4      Ref T/ ProblemfieldID 7

    6A6C4C2A-7328-4A91-B598-6F629C905A86               98                                               88

                                                                                    

    I could say formula = Comp T - Ref t.

    But I do not know how to transpose or rotate the table that way.

     

     

     

    • ImkeF's avatar
      ImkeF
      Community Champion
      9 years ago

      Probably a pivot-operations is what you're looking for:

      Check column "ProblemFieldID" -> Transform -> Pivot Column -> Choose "NumVal" in Value-Field.

       

      This will return a table with 1 additional column per ProblemFieldID which you then can build your calculations on.

      • Lilia's avatar
        Lilia
        Helper I
        9 years ago

        ImkeF Hello 

        Thank you for trying t help me. It works perfectly. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Lilia,

      In addition to other’s post, you can directly create a column with the following formula in your current table.

      Tem = 
var previous= CALCULATE(MAX(Table1[NumVal]),FILTER(Table1,Table1[ProblemID]=EARLIER(Table1[ProblemID])&& 'Table1'[ProblemFieldID]>EARLIER('Table1'[ProblemFieldID])))
return
IF(ISBLANK(previous),BLANK(), 'Table1'[NumVal]-previous)
 )))))))


      Regards,