How do I show my closest 3 competitors on a map?

user_6614519 0
Viewed 73

I work for a retailer and would like to create a map in Power BI for all of our stores and their 3 closest competitors, like the example below (Ideal Output). 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 the example data below (My Data).

Thanks,

Mark

Ideal Output: Ideal Output

My Data My Data

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

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

Table 3: Our stores and their 3 closest competitors.

dax powerbi gis mapping
1 Answers

Here's the solution that comes to mind for me. Create a new table that combines information from all 3 of the tables provided. It will be formatted like your Table 1 or Table 2, but with two new columns, Type and Slicer Store.

Type will specify whether the row is your own store or a competitor. Slicer Store will specify which rows should be displayed when that store's name is selected in the dropdown slicer.

Each row from your Table 3 will have 4 rows in the new table, one for each of the columns. The Slicer Store column in your new table will contain the Store Number from each row in Table 3. The Type column in your new table is self-explanatory.

You will end up with something like the following.

Number Name Address Latitude Longitude Type Slicer Store
JL123 ... ... ... ... Store JL123
C1 ... ... ... ... Competitor JL123
C2 ... ... ... ... Competitor JL123
C3 ... ... ... ... Competitor JL123
JL456 ... ... ... ... Store JL456
C2 ... ... ... ... Competitor JL456
C3 ... ... ... ... Competitor JL456
C4 ... ... ... ... Competitor JL456

Now for how to use it in Power BI. Create a single-select dropdown slicer using your new Slicer Store column. Then create your map visual using the rows in your new table. Use your new Type column as the Legend/Category. This allows you to color stores vs competitors differently in the visual.

Bing bang boom, you're done. Note that you may have "duplicate" rows for competitors in your new table. For example, if JL123 and JL456 were physically located right next to each other, then rows for C1, C2, and C3 would each appear twice in your new table. The Slicer Store would be different (JL123 or JL456) for these rows, though.

How you create the new table, either manually or with some sort of script, is the hard part.

user_178844140
Related