Calculating difference between consecutive rows

[Lilia]

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

As shown in the sample data, I have many “Problem ID”, and cumulative values of column “NumVal” I have to do extraction based on problem Field id and Problem ID column.

For instance

Problem ID 6A6C4C2A-7328-4A91-B598-6F629C905A86 = NumVal(ProblemFieldID=4 ) - NumVal(ProblemFieldID=7 ) will give me the temperature of that problem

The data is in no particular order, and I would like to be able to find the temperature for particular problemID by calculating the difference between two consecutive rows.

Br,

[Greg_Deckler]

When you say no particular order, do you mean that the row you might want to subtract might be 10 previous or 6 previous or is it always the previous row?

[Lilia]

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

[Greg_Deckler]

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]

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

[v-yuezhe-msft]

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

[ImkeF · Accepted solution]

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]

@ImkeF Hello 

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