Overview
- Understand how to create map-based visualizations in Power BI
- Explore and compare Bing and ArcGIS maps in Power BI
Introduction
In this article, we will learn how to create map-based visualizations in Power BI. While there are multiple options to create maps in Power BI, we will explore the features provided by Bing Maps and ArcGIS maps.
In recent years, digital mapping has lead to a paradigm shift in information visualization. The development and growth of GIS (Geographic Information Systems) tools have been a contributor. Nowadays, you would hardly come across a dashboard that does not contain a map-based visualization. For instance, search for COVID-19 statistics on the internet. You would most likely land on a web page that has a map of a region on which the figures are displayed.
Microsoft’s proprietary product Power BI is a powerful business intelligence tool that provides you with the capability to prepare map-based visualizations with a few clicks. In addition to the ease of use, it also gives you a host of options to choose from to make maps. For instance, Bing Maps powered by Microsoft serves most of our map-making tasks in Power BI.
However, Power BI has an additional plug-in for ESRI’s ArcGIS maps that gives you a completely different experience for plotting spatial data. As we explore both these map service providers’ features, I leave it to the user’s discretion to take their call while creating spatial data visualization.
Note: I will assume some prior familiarity using the Power BI Desktop platform. For a beginner-friendly introduction to Power BI, check out these blogs:
Table of Contents
- Understanding the data
- Data import into Power BI
- Setting the geo-location
- Create a Bing Map in Power BI
- Format Bing Maps
- ArcGIS Map in Power BI
- Map layers with ArcGIS
Understanding the data
Before we dive into creating map-based visualizations, let’s look at the dataset we will use for our analysis. Those who have worked with Tableau must be already familiar with the sample US Superstore data. The dataset records the orders placed by customers across the Superstore chain of stores in the USA.
The variables present in the data set are order ID (order identification number), order and shipment Date, mode of shipment, customer to whom the order was shipped, the customer’s geographical location, product bought, and the sales financials. In the picture below, we show a snapshot of the US Superstore data. For purposes of practice, I have uploaded the dataset here. We will use the Orders sheet from the Excel file.
As you may have already guessed by now, we will use the customers’ geographical information to showcase our analysis. For instance, our interest columns are the State, City, and Postal Code, along with the Country (United States), of course. We will use this information to showcase which regions in the US are the most significant contributor to sales and where Superstore can improve its performance.
Sales revenue and gross margin will be used to track the performance of these regions. Thus, our first task would be to import this Excel sheet into Power BI. In this case, I am using the desktop Power BI version, commonly known as Power BI Desktop; however, you are free to use the cloud version (Power BI Service) to visualize the key performance indicators (KPIs) by geographical regions in the USA.
Data import into Power BI Desktop
As previously mentioned, we will first import the Excel file using the Data Import menu in Power BI Desktop. Once you fire up Power BI’s desktop app, click on Get Data > All > Excel > Connect.
Locate the Sample Superstore.xlsx file saved on your machine and click Open. This will display the Navigator, where you can select the sheets you want to import into your workspace. Click on the Orders sheet, check the data snapshot shown for proper file loading, and then click on Load.
Once loaded into your Power BI workspace, you will see the column present in the dataset on the app’s Fields pane. You can also go to Data View to see the observations.
Setting the geo-location
Power BI has detected all the text-based location fields like Country, State, and City as text fields; and the Postal Code as a numeric field. But, these are geo-locations. To change how Power BI identifies these columns, we need to go to the Data View, select the appropriate column, and change these fields’ Properties. Convert the Data Category of the Country column to Country, State column as State or Province, City column to City (shown below), and Postal Code column to Postal Code.
Power BI will recognize these fields as geo-locations. Hence, we can now use a map-based visualization to look at the performance across regions. You may also check the Fields pane to see a geo-location tag appearing in front of each of these fields.
Lastly, we need to create a Measure of Gross Profit % (Sales/Profit) to help track the company’s profitability across any summarization level (in our case, City level). To achieve this, click on the New Measure icon on the Table Tools menu and write the following formula.
Create a Bing Map in Power BI
We are now ready to create our first map in Power BI. Go to the report view and click on Map visualization. This populates a Microsoft-powered Bing Map on your report.
On the Location well, drop Country, State, and City fields. Although maps are best plotted when we have latitude and longitude fields, Power BI can plot the charts based on the regions’ names. On the Size well, drop the Sales field. On the Tooltips well drop Gross Profit % field. This creates a US map that can be drilled down to the city level (see the drill-down button highlighted below). The bubbles’ size represents the Sales of the region level at which we are viewing the map.
We will view the map at the city level that looks like what is shown below. As we hover over the bubbles, we can see the cities with high sales value; New York City on the East Coast, San Francisco, Seattle, and Los Angeles on the West Coast.
Format Maps – Adding another dimension
Although the bubbles’ size represents total sales for the city, we may use the bubbles’ color to plot another dimension of Gross Profit %. To achieve this, go to the Format tab of the chart and under the Data Colors options, click on the functions button that defines the bubbles’ color scheme. You will notice, the Default Color shows the blue color of the bubbles.
The functions button controls the way the colors of the bubbles are defined. You may choose the color scale or rule-based coloring on Gross Profit %; that is, the margin percentage would represent the bubbles’ color. Below, I have defined a rule that limits the bubbles’ color on a linear scale of Gross Profit %. All the non-profitable cities are colored red, and the highly profitable ones are colored green.
Applying this rule to define the colors of the bubbles displays the map as shown below. What do you notice in the chart below?
- Cities in the Northern part of the East Coast are the most profitable ones, especially New York City with high revenue and high profitability.
- San Francisco and Los Angeles generate high revenue, but their margin is 10% – 20%.
- Most of the regions in Texas, Florida, Pennsylvania, and Colorado are the worst performers as they have negative margins.
Thus, map-based visualizations like this can pinpoint the cities that are bringing in more business for the company, the ones that are profitable, and the ones where the company needs to bring in strategic changes.
ArcGIS Map in Power BI
ArcGIS is a proprietary ESRI tool that provides GIS tools to create, manage, and analyze geographic information in a map or geographical database. It is one of the widely used software by GIS experts. Considering that, ESRI has integrated ArcGIS Maps for Power BI. To create an ArcGIS map in Power BI, click on the ArcGIS map visualization. If you don’t find it, click on the action button to Get more visuals and search for ArcGIS maps.
Remember that, unlike Bing Maps, ArcGIS maps don’t allow you to create hierarchical maps. So you will only be able to use the City field in the Location well. This may plot bubbles worldwide where there are similar city names as given in the Superstore data. To plot only US cities, click on the Expand Map Tools button on the chart’s top-left corner and click on Layer List. Further, click on the action button next to the City field and click on Location type. Here, you can inform ArcGIS that the location belongs to only one country, the United States, then click OK.
This operation plots all the points within the US. We can bring the Gross Profit % as a third dimension to represent the bubbles’ color ramp. This is how the default chart of ArcGIS looks like. The color shades don’t help us differentiate between more profitable and less profitable cities. Neither have we set the rules to define the color schemes.
Formatting ArcGIS Maps
To format ArcGIS Maps, we have to use the Layers List button on the chart’s left corner. Click on Symbology to know about the current format of the chart.
The divergent color ramp chosen to display the Gross Profit % is currently based on the variable’s distribution. Suppose we want to change the ramp based on profitability or quantiles; we can do that under Symbol Color, where light shades represent unprofitable cities and darker green shades, the profitable ones.
The map below well represents the distribution of Sales and margin by city.
Map layers with ArcGIS
Another interesting feature provided by ArcGIS Maps is the reference layers of demographics within a city or state. This provides context to the data. It helps the business to create targeted strategies for each region. Having a reference layer of the population’s incomes within a region can suggest the company to focus on areas with higher disposable incomes. You can add demographic and reference layers based on income, population, or weather that are already included free with ArcGIS distribution.
To add reference layers, click on Analysis Tools and go to Reference Layer, where a host of options are available.
The below map gets displayed after choosing the USA per capita income. Given the reference layer, it is quite easy to spot regions like Pennsylvania (high per capita income) where the business can advertise high-margin products. Moreover, the company can also target to increase its presence in Denver to earn higher revenue. Thus, you will appreciate the use of reference layers while using map-based visualizations.
End Notes
In this post, we looked at some interesting ways to plot map-based visuals using Microsoft Bing Maps and ArcGIS Maps. I feel Bing provides clean visuals when the task is to report KPIs on a simple map.
However, when you need to deep-dive and find improvement areas or take strategic initiatives based on geographic visualizations, ArcGIS Maps can help. Feel free to let me know what you feel. You can further explore ArcGIS Maps and let me know what more features it supports.
