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

[Anonymous]

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

[Greg_Deckler · Accepted solution]

@Anonymous- Perhaps try something like:

Measure = 
VAR __currentID = MAX([ID])
VAR __tmpTable = SUMMARIZE(ALLSELECTED('Earlier'),[ID],"__Value",SUM('Earlier'[Value]))
VAR __tmpTable1 = ADDCOLUMNS(__tmpTable,"PreviousID",MAXX(FILTER(__tmpTable,[ID]<EARLIER([ID])),[ID]))
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"PreviousValue",MAXX(FILTER(__tmpTable1,[ID]=EARLIER([PreviousID])),[__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.