Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
@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])
@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])
Awesome, thank you! This worked well for me - Index tip was great, hadn't noticed that in PQ. Very helpful!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |