Advanced Tableau maps, like the one below, can sometimes be overwhelming to interpret. Each point on the map shows an individual sale. A stakeholder may pose the question, “Where is the center of this data?” They want to know where, if you were to average all these points, it would fall. Perhaps you are interested in opening additional distribution centers or looking to change your shipping strategy. From this visual alone, that is unidentifiable. In this blog, I am going to show you how to calculate in Tableau the average latitude and longitude weighted by sales.

Learn more advanced mapping techniques with Playfair+

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

View / Interact / Download

 

It may feel tempting to simply drag your latitude and longitude to the view and set the aggregation to “Average”. While that may be okay for small subsets of the globe, the larger the area you are using, the more error you are exposing yourself to. The reasoning, simply put, is that the distance between two identical longitudinal coordinates can be thousands of kilometers at the equator but only a couple of kilometers closer to the poles.

New to Playfair Data?

Watch our 90-second intro video and receive tips on making your data usable.
 
Watch Video

To get a more accurate average, I am going to convert the latitude and longitude into cartesian coordinates. Cartesian coordinates are the classic (x,y,z) coordinates and can be used since each three-dimensional point is a uniform distance from one another.

Cartesian coordinates visual

Converting the coordinates

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

Converting the cartesian coordinates

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]))

X calculation

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

Y calculation

6371*sin(radians([Latitude]))

Z calculation

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:

Getting the weighted average for the advanced Tableau map

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

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

Weighted X Average calculation for advanced tableau map

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

Weighted Y Average calculation for advanced tableau map

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

Weighted Z Average calculation for advanced tableau map

 

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:

Converting back to geographic coordinates

In Tableau the formulas are:

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

Average Lat calculation for advanced tableau map

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

Average Lon calculation for advanced tableau map

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.

Make your data more usable with our free newsletter.

Get monthly tips and tutorials:

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.

Using cartesian vs non cartesian coordinates for advanced tableau mapping

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.

Advanced tableau mapping distance calculation

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

Become a member

Get access to this related video & more!

Become a Member

Related Content