Forum Discussion
Difference between rows filtering by ID row
Hi, I'm trying to figure out a way of calculating the difference between two rows of data. Here's an example of what my data looks like:
| BioreactorID | Acid1 | Index | Index.1 |
| 1 | 0 | 0 | 1 |
| 2 | 0 | 1 | 2 |
| 3 | 0 | 2 | 3 |
| 4 | 0 | 3 | 4 |
| 1 | 1 | 4 | 5 |
| 2 | 1 | 5 | 6 |
| 3 | 1 | 6 | 7 |
| 4 | 1 | 7 | 8 |
| 1 | 2 | 8 | 9 |
| 3 | 1 | 9 | 10 |
| 2 | 2 | 10 | 11 |
| 4 | 1 | 11 | 12 |
| 3 | 2 | 12 | 13 |
| 1 | 3 | 13 | 14 |
| 2 | 2 | 14 | 15 |
| 4 | 2 | 15 | 16 |
| 4 | 3 | 16 | 17 |
| 3 | 3 | 17 | 18 |
| 2 | 3 | 18 | 19 |
| 1 | 4 | 19 | 20 |
I'm interested in the difference in consecutive rows of the 'Acid1' column. However, I also want to filter this calculation by the 'BioreactorID'. For example, taking only 'BioreactorID' == 1:
| BioreactorID | Acid1 | Index | Index.1 | Desired output |
| 1 | 0 | 0 | 1 | 0 |
| 1 | 1 | 4 | 5 | 1 |
| 1 | 2 | 8 | 9 | 1 |
| 1 | 3 | 13 | 14 | 1 |
| 1 | 4 | 19 | 20 | 1 |
'Desired output' is simply calculated by 'Acid1' - previous 'Acid1'
Overall, I want the resulting dataset to look like this:
| BioreactorID | Acid1 | Index | Index.1 | Desired output |
| 1 | 0 | 0 | 1 | 0 |
| 2 | 0 | 1 | 2 | 0 |
| 3 | 0 | 2 | 3 | 0 |
| 4 | 0 | 3 | 4 | 0 |
| 1 | 1 | 4 | 5 | 1 |
| 2 | 1 | 5 | 6 | 1 |
| 3 | 1 | 6 | 7 | 1 |
| 4 | 1 | 7 | 8 | 1 |
| 1 | 2 | 8 | 9 | 1 |
| 3 | 1 | 9 | 10 | 0 |
| 2 | 2 | 10 | 11 | 1 |
| 4 | 1 | 11 | 12 | 0 |
| 3 | 2 | 12 | 13 | 1 |
| 1 | 3 | 13 | 14 | 1 |
| 2 | 2 | 14 | 15 | 0 |
| 4 | 2 | 15 | 16 | 1 |
| 4 | 3 | 16 | 17 | 1 |
| 3 | 3 | 17 | 18 | 1 |
| 2 | 3 | 18 | 19 | 1 |
| 1 | 4 | 19 | 20 | 1 |
where the 'Desired output' calculates the difference in 'Acid1' for each 'BioreactorID' independently.
I have managed to do this by manually creating separate tables for each BioreactorID, but I'd prefer to have a scalable solution as the number of BioreactorID's won't always be the same. Also note that BioreactorID won't always be in order (1,2,3,4...).
Any help with this would be much appreciated, thanks!
You could create a calculated column like
Diff = VAR currentBioReactor = 'Table'[BioReactor] VAR currentIndex = 'Table'[Index] VAR currentValue = 'Table'[Acid1] VAR prevValue = SELECTCOLUMNS ( TOPN ( 1, FILTER ( ALL ( 'Table' ), 'Table'[BioReactor] = currentBioReactor && 'Table'[Index] < currentIndex ), 'Table'[Index] ), "@val", 'Table'[Acid1] ) RETURN currentValue - prevValue
2 Replies
You could create a calculated column like
Diff = VAR currentBioReactor = 'Table'[BioReactor] VAR currentIndex = 'Table'[Index] VAR currentValue = 'Table'[Acid1] VAR prevValue = SELECTCOLUMNS ( TOPN ( 1, FILTER ( ALL ( 'Table' ), 'Table'[BioReactor] = currentBioReactor && 'Table'[Index] < currentIndex ), 'Table'[Index] ), "@val", 'Table'[Acid1] ) RETURN currentValue - prevValue
