Plotting stores and their competitors on a map

[m5edward]

Hi Everyone,

I work for a retailer and would like to create a map for all of our stores and their 3 closest competitors, like the example below. The blue dot is our store and the red dots are our competitors. Ideally, the map would change automatically when a different store is selected in a drop-down slicer. Please see example data below.

Thanks,

Mark

My Data

Table 1: All of our stores. Very basic. Every store has its own unique number, along with basic store details including geocodes. Example below.

Example Stores.xlsx
	A	B	C	D	E
1	Store Number	Store Name	Store Address	Latitude	Longitude
2	JL123	Flagler	1051 W Flagler St, Miami, FL 33130, USA	25.7735880	-80.2123746
3	JL456	3rd Ave	1324 NW 3rd Ave, Miami, FL 33136, United States	25.7869713	-80.1999601
4	JL789	2nd Ave	2634 NE 2nd Ave, Miami, FL 33137, USA	25.8020217	-80.1908089
Stores

Table 2: All competitors. Every competitor has a unique number along with basic information including geocodes. Example Below.

Example Competitors.xlsx
	A	B	C	D	E
1	Competitor Number	Competitor Name	Address	Latitude	Longitude
2	C1	Shoe Box	1073 W Flagler St, Miami, FL 33130, USA	25.773723	-80.2125866
3	C2	Shoes R Us	1062 SW 1st St, Miami, FL 33130, USA	25.772599	-80.2126713
4	C3	Tom's Shoe Store	609 Brickell Ave, Miami, FL 33131, United States	25.772631	-80.2096832
5	C4	Shoe Laces	1360 NW 3rd Ave, Miami, FL 33136, USA	25.787999	-80.2006365
6	C5	Sally's Shoes	1039 NW 3rd Ave, Miami, FL 33136, United States	25.786385	-80.2005246
7	C6	Jose's Shoe Store	1201 NW 1st Pl, Miami, FL 33136, USA	25.786016	-80.1983815
8	C7	Foot Locker	189 NE 26th St, Miami, FL 33137, USA	25.802056	-80.1914622
9	C8	Athelte's World	10 NE 27th St, Miami, FL 33137, United States	25.802581	-80.1949045
10	C9	Champ's	2601 N Miami Ave, Miami, FL 33137, USA	25.802581	-80.1949045
Sheet1

Table 3: Our stores and their 3 closest competitors.

Example Distances.xlsx
	A	B	C	D
1	Store Number	Competitor 1	Competitor 2	Competitor 3
2	JL123	C1	C2	C3
3	JL456	C4	C5	C6
4	JL789	C7	C8	C9
Sheet1

 

[Rijnsent]

Hi Mark,
your question comes down to two questions:
1) calculate which shops are closest to each of your locations. To do that, you could use an excel formula. I made a VBA project for this, but you can take the raw formula on this page too: GitHub - krijnsent/geo_vba: A geographic toolkit/library for VBA, excel etc.
2) to plot it on a map. Unfortuntately, Excel is a bit tough when it comes to map charts. You could e.g. try this tutorial: 5 Steps to Easily Use Excel 3D Maps (Bonus Video Included)
Good luck,
Koen

 

[m5edward]

Hi Rijnsent,

I am trying to do this in PowerBI, and I know which competitors are closest to my store (see table 3). And those competitor store numbers have geocodes.

Conceptually, everything I need is in the 3 tables provided. I just need to know how to link them together and plot on the map.

Thanks,

Mark