cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
samdep
Helper II
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
amitchandak
Super User
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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

View solution in original post

2 REPLIES 2
amitchandak
Super User
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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

View solution in original post

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors