Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SansNom
Frequent Visitor

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

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 = DIVIDEPI(), 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).

SansNom_0-1718956093906.png


Thank you in advance for your reply.

1 ACCEPTED SOLUTION
SansNom
Frequent Visitor

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

View solution in original post

1 REPLY 1
SansNom
Frequent Visitor

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.