cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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 : 

IDValueType
431219Major
431243Major
4312337Major
430241Minor
4302337Minor
429130Major
429157Major
429218Major
429242Major
4292337Major
428246Minor
4282337Minor
4270Major
4271Major
4273Major
4275Major
4276Major
4277Major
4279Major

 

So I have grouped this data into matrix: 

IDValueType
4312799Major
4302578Minor
4293084Major
4282583Minor
42731Major

 

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

IDValueTypeDiff
4312799Major221
4302578Minor-506
4293084Major501
4282583Minor2552
42731Major31

 

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 : 

IDValueTypeDiff
4312799Major-285
4293084Major3053
42731Major31

 

whereas I am getting this: 

IDValueTypeDiff
4312799Major221
4293084Major501
42731Major31

 

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

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


1 REPLY 1
Super User
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 __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.

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!