Advanced Tableau Mapping Series: Calculating Weighted “Center of Gravity”

Converting the coordinates

To get your (x,y,z) coordinates, use the following formulas:

The r is your radius, so in this instance, r would be the radius of the Earth which is 6,371 km. Also, note that to convert into Cartesian coordinates, your latitude and longitude need to be in radians. The data set I am using has its coordinates in degrees, so I will need to add the step of converting from degrees to radians in Tableau. In Tableau, the formulas will be the following:

6371*cos(RADIANS([Latitude]))*cos(radians([Longitude]))

6371*cos(RADIANS([Latitude]))*sin(RADIANS([Longitude]))

6371*sin(radians([Latitude]))

If your coordinates are already in radians, leave out the RADIANS function.

Getting the weighted average for the advanced Tableau map

Now we are going to calculate our average (x,y,z) coordinates. I am going to weight my average by sales to get a weighted center of gravity. The formula for a weighted average is as follows:

Where w is the weight and X is the value being averaged. In Tableau the formulas will be:

sum([Sales]*[X])/sum([Sales])

sum([Sales]*[Y])/sum([Sales])

sum([Sales]*[Z])/sum([Sales])

Converting back to geographic coordinates

With our weighted averages, we will need to convert back from Cartesian coordinates to latitude and longitude so that we are able to get the coordinates on the map. The formulas to convert back are:

In Tableau the formulas are:

DEGREES(asin([Weighted Z Average]/6371))

DEGREES(ATAN2([Weighted Y Average],[Weighted X Average]))

Since the results are in radians, I am wrapping the equations in the DEGREES function to convert back into degrees.

The results of this advanced Tableau mapping technique

Now we have our weighted average latitude and longitude. In this data set, we get a latitude of 23.2° and a longitude of 66.97° which falls a little off the coast of India and Pakistan.

If we had just used the average of the values of latitude and longitude, we would have gotten a latitude of 24.99° and longitude of 44.84°, which falls in the middle of Saudi Arabia. Simply averaging the longitude results in a far different result and in this case, placing the center of gravity in a far different location.

To further demonstrate the difference between the results of these two calculations, I am using Tableau’s DISTANCE function to calculate the kilometers between the points we calculated using Cartesian coordinates and the points averaged in Tableau.

The distance between the two points is 2,256 kilometers, which is not an inconsequential distance!

There are other, more involved ways to calculate an average latitude and longitude with even more accuracy. If you are trying to do something like launching a satellite into orbit, this method may not get you where you need to go. However, it should be suitable for most of your business applications.

Stay after it,
Maddie