Building the Walmart Navigator
A Power BI Dashboard Walkthrough
Creating an interactive dashboard like the Walmart Navigator involved a series of steps — from wrangling data to fine-tuning the final user experience. This dashboard allows users to locate the nearest Walmart stores based on their city, distance threshold, and preferred units. Here’s a detailed summary of processes it took to achieve this.
Defining the Objective
The goal was straightforward: create a dashboard that calculates and visualizes the proximity of Walmart stores to a user-selected city. The final solution needed to:
- Allow users to select a city and set a distance threshold.
- Display the nearest stores and provide actionable insights, such as clickable store links.
- Be visually engaging, intuitive, and accurate.
Collecting and Preparing the Data
The dataset contained two key tables:
- Walmart Store Locations: Included store names, latitudes, longitudes, and other store details.
- US Cities: Contained city names, state names, and their corresponding coordinates.
🔗Link to dataset here
Data Wrangling Steps:
- Combining City and State: I created a column in Power Query to combine city and state in a user-friendly format, e.g., “Dallas, TX.”
- Creating Unique Store Names- Data Model Relationship: Since some Walmart stores shared the same name in different locations, I concatenated the store name, city, and state into a unique identifier: `Store Name # City, State`.
- Generating Store URLs: Using the pattern `https://www.walmart.com/store/{Store ID}-{City}-{State}`, I created a column to provide clickable links to store pages.
Calculating Distances Using the Haversine Formula
Why the Haversine Formula?
The Haversine formula calculates the great-circle distance between two points on Earth’s surface, accounting for its curvature. This ensures accurate measurements, even over long distances. It’s perfect for geospatial projects like this.
Implementing the Formula in DAX:
Here’s how the formula was translated into a DAX measure:
Closest Distance =
VAR Lat1 = SELECTEDVALUE(uscities[city_lat])
VAR Lng1 = SELECTEDVALUE(uscities[city_lng])
VAR Unit = SELECTEDVALUE(Settings[DistanceUnit], "km") - Choose "km" or "miles"
RETURN
MINX(
walmart,
VAR Lat2 = walmart[latitude]
VAR Lng2 = walmart[longitude]
VAR P = DIVIDE(PI(), 180)
VAR A = 0.5 - COS((Lat2 - Lat1) * P) / 2
+ COS(Lat1 * P) * COS(Lat2 * P) * (1 - COS((Lng2 - Lng1) * P)) / 2
VAR DistanceKm = 12742 * ASIN(SQRT(A))
VAR DistanceMiles = DistanceKm * 0.621371
RETURN IF(Unit = "miles", DistanceMiles, DistanceKm)
)Key Challenges:
- Ensuring the formula handled filters correctly (e.g., selected cities and states).
- Debugging edge cases where distances were incorrectly calculated (e.g., mismatched coordinates).
Designing the Dashboard Layout
Map Visualization:
I opted for an Interactive map(native chart) that plots Walmart store locations.
Stores were color-coded:
- Yellow: Nearest store.
- Blue: Top 5-nearest store.
- Gray : All other stores.
Supporting Table:
The table provided additional details:
- Store name.
- Distance from the selected city.
- A clickable URL to the store’s page.
Dynamic Summary:
To keep things user-friendly, I added a summary card displaying the user’s:
- Selected city.
- Distance threshold.
- Unit of measuremen(km/miles)
Styling Choices:
- I chose a greyscale map for a clean, professional look.
- Used minimalistic fonts and color schemes for clarity.
Creating Key Measures
Counting Stores Within Proximity:
A simple measure to count stores within the user-defined distance:\
Stores Within Threshold =
CALCULATE(
COUNTROWS(walmart),
FILTER(
walmart,
[Closest Distance] <= 'Maximum Distance'[Maximum Distance Value]
)
)Dynamic Welcome Message:
To make the dashboard more clear and intuitive, I added a measure at the top:
Welcome Message =
"Welcome! You've selected " &
SELECTEDVALUE(uscities[city]) & ", a distance limit of " &
'Maximum Distance'[Maximum Distance Value] & " " &
SELECTEDVALUE(Settings[DistanceUnit]) & "."Takeaways and Future Improvements
What Worked:
- The combination of geospatial data and user interactivity made the dashboard highly functional.
- Implementing the Haversine formula in DAX showcased how mathematical concepts can be applied in BI tools.
What’s Next:
- Incorporating user reviews or ratings for stores.
- Expanding the scope to include other retail chains.
Final thoughts
I made this dashboard with the intention of sharing ideas for the broad applications across various fields:
- Retail and Logistics: In this, companies can analyze store accessibility for optimizing delivery routes.
- Travel and Planning: Users can plan trips or errands by identifying nearby stores.
- Customer Experience: Enhances customer convenience by making location-based decisions simpler and faster.e.g closest agent location.
Reference
