Best way to calculate percent error based on two different rows

[Anonymous]

I have a table that consists of multiple rows of pressure readings for multiple different types of devices. One of the devices is a reference device. Each pressure reading for all devices, including the reference, is associated with a pointid. For example:

PointID	Model	Pressure Reading
1	A	5
1	B	4
1	Reference	6
2	A	1
2	B	2
2	C	6
2	Reference	5

I need to get the percent error for each pressure reading, where percent error is defined as:

(pressure reading - reference pressure)  / reference pressure * 100. For example, the percent error for the first row would be 5 -6 / 6 *100 = -16.67 percent.

What is the best way to search for the associated reference pressure for each row? My initial thought is to, in power query, duplicate the pressure table, and filter out all rows except the reference pressure. This is my reference pressure table. Then to create a calculated column in the original table, where I go through each row and do a lookup for the reference pressure with the same point id. Is there a better way to go about this?

[amitchandak]

@Anonymous ,

A new column =

var _ref = maxx(filter(Table, [PointID] = earlier( [PointID] ) && [Model] ="Reference"  ) , [Pressure Reading] )

return

divide([Pressure Reading] -_ref, _ref) *100 

Divide(

[Anonymous]

Sorry, I am not quite following this. Are you suggesting a place this code in the customn column window, or in the advanced editor? If in the custom column window, I get a token eof expected error when I attempt to define a variable as you have done.