cancel
Showing results for
Did you mean:
Highlighted
Member

## Dynamic Consecutive Difference between rows based on filter selection and a primary column

Hi All,

I have a very interesting problem to share for which I am unable to find a solution. This is very much similar to calculating difference between consecutive rows. But the catch here is this calculation has to be dynamic based on filter selection.

Original data looks like this :

 ID Value Type 431 219 Major 431 243 Major 431 2337 Major 430 241 Minor 430 2337 Minor 429 130 Major 429 157 Major 429 218 Major 429 242 Major 429 2337 Major 428 246 Minor 428 2337 Minor 427 0 Major 427 1 Major 427 3 Major 427 5 Major 427 6 Major 427 7 Major 427 9 Major

So I have grouped this data into matrix:

 ID Value Type 431 2799 Major 430 2578 Minor 429 3084 Major 428 2583 Minor 427 31 Major

I want calculate difference between two consectuive rows which should ideally look like:

 ID Value Type Diff 431 2799 Major 221 430 2578 Minor -506 429 3084 Major 501 428 2583 Minor 2552 427 31 Major 31

I am able to get the above result. But now I want to select filter based on type. E.g say Major is selected. I want the result to look like :

 ID Value Type Diff 431 2799 Major -285 429 3084 Major 3053 427 31 Major 31

whereas I am getting this:

 ID Value Type Diff 431 2799 Major 221 429 3084 Major 501 427 31 Major 31

I tried creating a measure which will reset itself based on filter selection:

`Measure = CALCULATE(COUNT(Grouped_Auto_QA_Basic_Analysis[Index]), FILTER(ALLSELECTED(Grouped_Auto_QA_Basic_Analysis), Grouped_Auto_QA_Basic_Analysis[Index] <= MAX(Grouped_Auto_QA_Basic_Analysis[Index])))`

But when I tried creating diff using formula:

```diff1 = var previndex = Grouped_Auto_QA_Basic_Analysis[Measure] -1
return
Grouped_Auto_QA_Basic_Analysis[campaign_nkw] - CALCULATE(VALUES(Grouped_Auto_QA_Basic_Analysis[campaign_nkw]),FILTER(ALLSELECTED(Grouped_Auto_QA_Basic_Analysis),Grouped_Auto_QA_Basic_Analysis[Measure] = previndex))```

I am not able to get desired result using the formula. Can someone help me calculating dynamic difference based on filter selection? I tried doing this using Index but didn't workout. I also tried using R code, failed again.

Thank you so much in advance. I am desperate to get this done as soon as possible.

Cheers,

Shruti

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Dynamic Consecutive Difference between rows based on filter selection and a primary column

@Shruti_Goyal94- Perhaps try something like:

```Measure =
VAR __currentID = MAX([ID])
VAR __tmpTable = SUMMARIZE(ALLSELECTED('Earlier'),[ID],"__Value",SUM('Earlier'[Value]))
VAR __tmpTable3 = ADDCOLUMNS(__tmpTable2,"Difference",[PreviousValue] - [__Value])
RETURN MAXX(FILTER(__tmpTable3,[ID]=__currentID),[Difference])```

'Earlier' is my table name. It's a bit verbose but I was going for step-by-step to better demonstrate the technique here. Definitely less verbose ways of solving this, but I think this demonstrates the technique in a manner that can be comprehended pretty easily.

If you put this Measure into a matrix, it will respect your ID filtering.

Proud to be a Datanaut!

Super User

## Re: Dynamic Consecutive Difference between rows based on filter selection and a primary column

@Shruti_Goyal94- Perhaps try something like:

```Measure =
VAR __currentID = MAX([ID])
VAR __tmpTable = SUMMARIZE(ALLSELECTED('Earlier'),[ID],"__Value",SUM('Earlier'[Value]))
VAR __tmpTable3 = ADDCOLUMNS(__tmpTable2,"Difference",[PreviousValue] - [__Value])
RETURN MAXX(FILTER(__tmpTable3,[ID]=__currentID),[Difference])```

'Earlier' is my table name. It's a bit verbose but I was going for step-by-step to better demonstrate the technique here. Definitely less verbose ways of solving this, but I think this demonstrates the technique in a manner that can be comprehended pretty easily.

If you put this Measure into a matrix, it will respect your ID filtering.