Error in the value of the subtotals of the row and column sums of a matrix

[Anonymous]

Hello,

I have a problem. In order to find out the distances between two places, I've created a matrix to get the information. The rows and columns are taken from two different sharepoint lists, because the columns represent the places of departure and the places of arrival. In both lists, I have the cities of the locations, as well as the latitude and longitude.

I calculate the distance as a measure between all the locations using this formula:

Kilometres = var Lat1 = MIN('Adresse 1'[Latitude])

var Lng1 = MIN('Adresse 1'[Longitude])

var Lat2 = MIN('Adresse 2'[Latitude])

var Lng2 = MIN('Adresse 2'[Longitude])

---- Algorithm here -----

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 final = 12742 * ASIN((SQRT(A)))

return final

Which I found on another power bi forum by doing some research, which gives me a consistent result on most of the values I looked at (of the order of a kilometre difference so that's good, the aim being to have only an approximation), but when I display the subtotals of the rows and columns, I don't have the right values, and I haven't found the solution on another forum (I may have searched incorrectly).

Thank you in advance for your reply.

[Anonymous · Accepted solution]

For those who will ask the question, I made a crossjoin to create a new table with all the possibilities :

AllCombinations =

SELECTCOLUMNS(

    CROSSJOIN(

        'Adresse 1',

        'Adresse 2'

    ),

    "1ID", 'Adresse 1'[ID],

    "1", 'Adresse 1'[Location],

    "1Latitude", 'Adresse 1'[Latitude],

    "1Longitude", 'Adresse 1'[Longitude],

    "2", 'Adresse 2'[ID],

    "2Latitude", 'Adresse 2'[Latitude],

    "2Longitude", 'Adresse 2'[Longitude]

)

and then I created a measure that allows me to get the total distances:

Distance =

VAR Lat1 = [1Latitude]

VAR Lng1 = [1Longitude]

VAR Lat2 = [2Latitude]

VAR Lng2 = [2Longitude]

VAR P = PI() / 180

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

        COS(Lat1 * P) * COS(Lat2 * P) * (1 - COS((Lng2 - Lng1) * P)) / 2

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

RETURN Final

I finally put the first IDs in line and the second in columns of my matrix and put the distance as values, which gave me the right subtotals directly 
My AllCombination table is very large, but it is the only solution I found to solve the problem