Rank based on distance measure

[tanvi_m]

I have three tables which i am using in order to calculate the distance between two lat/longs. 

Table A : 

X, lat , long

Table " From A"  : 

From A = SUMMARIZECOLUMNS(

'A'[X],

'A'[LATITUDE],

'A'[LONGITUDE]

)

Table B:

 SITE ID CI128 HXL = SUMMARIZECOLUMNS('A'[X])

measure to calculate distance : 

mile =

var lat1 = min('From A'[LATITUDE])

var lng1 = min('From A'[LONGITUDE])

var lat2 = min ('A'[LATITUDE])

var long2 = min ('A'[LONGITUDE])

var P = divide (pi(),180)

var A = 0.5 - cos((lat2-lat1)*p)/2+

cos(lat1*p)*cos(lat2*p)*(1-cos((long2-lng1)*p))/2

var final = 0.621 * (12742 * asin((sqrt(A))))

return final

Now I have my distance calculated perfectecly when I put following entries in the table frmat 

X(Source - coming from table A) | X(Target - coming from table "From A) | mile  . 

Now I need to write a measure in order to rank them based on their distances 

X(source) | X(Target) | mile |  RANK (?) 

Could anyone please help me with the same ? 

Thanks 

[tanvi_m]

Tom, 

Thankyou for the response . 

Here’s the complete details on data structure 

Table A : 

LONGITUDE	LATITUDE	X
-96.6	32.9	A
-96.9	33	B
-96.5	32	C
-97.7	31	D
-96	32.2	A
-97	32.1	B
-96.5	33	E
-97.7	31	X

Table B = SUMMARIZECOLUMNS('A'[X])

From A = SUMMARIZECOLUMNS(

'A'[X],

'A'[LATITUDE],

'A'[LONGITUDE]

)

OUTPUT TABLE : 

X (coming from table A)	X(Coming from table From A)	Distance(miles)
A	C	14.99815
C	A	14.99815
A	B	24.38354
B	A	24.38354

Now I need to rank column [ X(Coming from table From A) ] based on values in column " distance(miles)" . 

Hope this explains the query in details.. 

Thankyou 

Tanvi 

[TomMartens]

Hey,

please provide a pbix file that contains some sample data, but still represents the data model, upload the file to onderive, dropbox, and share the link (if you use xlsx for the data creation, upload the xlsx file as well).

Regards,

Tom