Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
@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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |