cancel
Showing results for
Did you mean:  Helper II

Absolute Value Difference Between Two Non-Consecutive Rows (Same IDs)

Hi PBI Community!

I have a table similar to the below:

DATE             ID          GPA

12/1/2021    ABCD     3.8

12/1/2021    ABCD     4.0

I am using tables in my visual and would like to include a flag for any variance in the GPA greater than 1.5 between the same ID/Student's GPA. So, in this case, ID: ABCD would have a variance of .2 -- The GPA's could come in on the same date, which is why I am having a bit of a struggle. Also, I'd like the absolute value, as opposed to perhaps a negative value.

I may put the .2 in the table itself or write a conditional expression that says something along the lines of 'if the variance is >= 1.5, then Flag, ""'

Thanks All - Very much appreciate all of the help provided in this community!

1 ACCEPTED SOLUTION  Super User

@samdep , This bit difficult. You need to add an index column in power query -

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

Then create a measure like

New column =
var _max = maxx(filter(Table, [Index] <earlier([Index])) , [Index])
return
[GPA] - maxx(filter(Table, [Index] = _max ) , [GPA])

or

New column =
var _max = maxx(filter(Table, [Index] <earlier([Index]) && [ID] = earlier([ID] )) , [Index])
return
[GPA] - maxx(filter(Table, [Index] = _max && [ID] = earlier([ID] ) ) , [GPA])

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
2 REPLIES 2  Super User

@samdep , This bit difficult. You need to add an index column in power query -

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

Then create a measure like

New column =
var _max = maxx(filter(Table, [Index] <earlier([Index])) , [Index])
return
[GPA] - maxx(filter(Table, [Index] = _max ) , [GPA])

or

New column =
var _max = maxx(filter(Table, [Index] <earlier([Index]) && [ID] = earlier([ID] )) , [Index])
return
[GPA] - maxx(filter(Table, [Index] = _max && [ID] = earlier([ID] ) ) , [GPA])

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!  Helper II

Awesome, thank you! This worked well for me - Index tip was great, hadn't noticed that in PQ. Very helpful!  