Calculate total of distance in measure

[JeroenN]

Hi,

I have this measure to calculate the distance between two points:

M_Distance =

VAR Lng1 = AVERAGEX(PC2_Plaats,PC2_Plaats[longitude])

VAR Lat1 = AVERAGEX(PC2_Plaats, PC2_Plaats[latitude])

VAR Lng2 = 4.413364504 

VAR Lat2 = 51.87001675

VAR P = DIVIDE( PI(), 180 )

VAR A = 0.5 - COS((Lat2-Lat1) * p)/2 + COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2

VAR X = 12742 * ASIN((SQRT(A)))

RETURN

    X

This works fine for each individual row in my data. But how can I sum the distance for the whole data set or a part of it?

The second column in this screenshot shows the measure and the third is a calculated column with the result I want. 

Hope you can help with this!

Thanks, Jeroen

[JeroenN · Accepted solution]

Found it!
Because I placed all variables in one table, I don't need to do a calculation on the longitude and latitude.
So the measure should be:

Total_Distance =

SUMX(

    Wensen,

    VAR Lng1 = Wensen[lon]

    VAR Lat1 = Wensen[lat]

    VAR Lng2 = 4.413364504

    VAR Lat2 = 51.87001675

    VAR P = DIVIDE(PI(), 180)

    VAR A = 0.5 - COS((Lat2 - Lat1) * P) / 2 + COS(Lat1 * P) * COS(Lat2 * P) * (1 - COS((Lng2 - Lng1) * P)) / 2

    VAR X = 12742 * ASIN(SQRT(A))

    RETURN X

)

Thanks for your help, Fowmy

[Fowmy]

@JeroenN 

This pattern measure should work for you:

Measure Name = 

SUMX(
	SUMMARIZE(
		TABLE,
		TABLE[Col1],
		TABLE[Col2]
	),
	[Measure]
)

[JeroenN]

Not sure what you mean. 
I think I need to adjust the blue lines 8 and 9. But in what way?

Total_Distance_II =

    SUMX(

    SUMMARIZE(

        Wensen,

        Wensen[lat],

        Wensen[lon]

    ),

    VAR Lng1 = AVERAGEX(wensen,wensen[lon])

    VAR Lat1 = AVERAGEX(wensen, wensen[lat])

    VAR Lng2 = 4.413364504

    VAR Lat2 = 51.87001675

    VAR P = DIVIDE(PI(), 180)

    VAR A = 0.5 - COS((Lat2 - Lat1) * P) / 2 + COS(Lat1 * P) * COS(Lat2 * P) * (1 - COS((Lng2 - Lng1) * P)) / 2

    VAR X = 12742 * ASIN(SQRT(A))

    RETURN

        X

)

[Fowmy]

@JeroenN 

I am not sure about the tables and the Lat/Long fileds that are attahed to them. You have a table named PC2_Plaats and another Wensen. Could you share a sample or dummy PBI file,

[JeroenN]

I made a small change to my dataset. All values are stored in the same table: wensen.