How to Build a Power BI Logistics Dashboard?

A logistics dashboard is an analytical tool that consolidates data from various sources to provide a unified view of logistics operations. It plays a crucial role in logistics management by offering real-time visibility into the supply chain, enabling informed decision-making, and enhancing operational efficiency.

Logistics dashboards aggregate data from transportation management systems, warehouse management systems, GPS tracking devices, and other sources. This consolidated data is then presented in an intuitive visual format that allows stakeholders to monitor performance, identify bottlenecks, and implement improvements. By using logistics dashboards, companies can achieve better coordination, reduce costs, and improve customer satisfaction.

Power BI is particularly suitable for creating logistics dashboards due to its customizable visualizations, ease of use, and scalability.

In this tutorial, you’ll learn how to build a comprehensive logistics dashboard from scratch in Power BI. You will first integrate GeoPostcodes’ precise location data with Power BI, build your dashboard using Power BI, and then visualize key logistics metrics to gain actionable insights.

Prerequisites

To follow this tutorial, you will need the following:

• A basic understanding of Power BI and how to use it
• A Power BI account
• Power BI Desktop
• A GeoPostcodes account. The free trial account will suffice for this tutorial, but keep in mind that it only provides sample datasets.
• A logistics dataset (eg order shipment records, delivery times, and route information). This tutorial includes a sample dataset so that you can follow along.

Identifying KPIs

Before diving into the technical aspects of building a dashboard, you need to identify the key performance indicators (KPIs) that the dashboard must track. Common logistics KPIs include the following:

• Delivery time: the time taken from order placement to delivery
• Route efficiency: the time taken to complete delivery routes of different lengths
• On-time delivery rate: the percentage of deliveries made on time
• Shipment volume: the total volume of shipments over a period

Depending on your use case, your dashboard will combine various KPIs like these to meet your needs. For example, in this tutorial, you will build an order delivery and routes dashboard that include the following:

• A map visualization that shows delivery routes and destinations
• A bar chart displaying delivery time by orders
• A line chart showing the route efficiency per day

However, you might also want to build something else, such as a performance metrics dashboard that includes the following:

• KPI cards summarizing key performance metrics, like overall orders, total delivery distance covered, and weight carried
• A summary table displaying the orders and freight payments by carrier type and name

Downloading the Datasets

To start, create a new project directory called power-bi-logistics-dashboards on your local machine. Next, create a subdirectory called dataset and download the sample logistics datasets for this tutorial from the following URLs into the dataset subdirectory:

• Delivery routes data
• Carrier data

To download the location dataset, open a browser and log in to your GeoPostcodes account with your credentials. Using this dataset from GeoPostcodes will improve the location accuracy of your Power BI visualizations that deal with spatial data.

From the landing page, click on Download Center and then on Postal database:

The next screen shows the postal datasets for various countries:

Even though GeoPostcodes provides comprehensive international data, this tutorial uses only data from the United States for the sake of simplicity.

Scroll down and click on United States to download its location dataset.

The downloaded file will be named something like GPC-POST-GEO-SAMPLE-US.zip. The zip file contains a CSV directory, from which you have to copy the file GPC-PLACES-GEO-SAMPLE-US.csv to the dataset subdirectory of this project. For simplicity, this dataset will be referred to as the GeoPostcodes dataset in the rest of this tutorial.

You should now have three files in your dataset directory:

1. carrier.csv
2. delivery_routes_data.csv
3. GPC-PLACES-GEO-SAMPLE-US.csv

Setting Up Power BI with Datasets

You’re now ready to start building your dashboard in Power BI. Launch the Power BI Desktop app from your machine:

If you’re using it for the first time, you need to sign in using your Power BI account.

To import the tutorial’s sample datasets into Power BI, click on Get data from other sources. A window will pop up with various data sources to choose from:

Select Text/CSV and click Connect. You will see a window that allows you to navigate through your machines’ directories:

Use this window to select the carrier.csv dataset file and click Open. You will see a window, as shown below, displaying the data from the chosen CSV file:

You can see that Power BI has automatically chosen Comma as the delimiter, but it couldn’t auto-identify the field names from the file content. Therefore, click Transform Data to get to the Power Query Editor window, which will allow you to carry out the data-transformation process:

Click the option Use First Row as Headers to select the first row as the header field names. Once that’s done, you should see the transformation applied to the dataset, as below:

Click Close & Apply to close the Power Query Editor window. You will now see the Power BI interface, as shown below:

On the right side, you can see the imported carrier dataset. At the top left, you can see the Save icon. Click it to save your work. When you’re prompted, give the file name logistics-dashboard and save it in the project directory.

You should see a screen similar to the one below, with the saved file name at the top:

Next, click on Get data and select Text/CSV to import the next dataset, delivery_routes_data.csv, by following the steps above. The only difference is that, this time, instead of clicking Transform Data, click Load as the header row is auto-identified by Power BI, as shown below, and the data is good as is for further use:

The load option will skip the Power Query Editor window and directly load the data from the CSV file into Power BI.

When you’re done importing the delivery routes dataset, click on Get data and select Text/CSV to import the next dataset, GPC-PLACES-GEO-SAMPLE-US.csv. You’ll again follow the same steps as before. You’ll have to click Transform Data to take you to the Power Query Editor window:

Click the Choose Columns option to select only those columns needed for this tutorial. In the window shown below, choose only the columns named postcode, latitude, and longitude, and then click OK.

You should see the transformed GeoPostcodes dataset as below:

Merging the GeoPostcodes Data with the Sample Logistics Data

Even though you have imported all three datasets, you don’t yet have the latitude and longitude information in the delivery routes dataset. To get this information, you will merge GeoPostcodes’ location data with the delivery route dataset in this section.

Select the delivery_routes_data in the left-side Queries window and click on Merge Queries to initiate the merge action:

The new merge window that pops up will have no options selected in the second drop-down box:

As shown in the image below, select the datasets and the column that must be merged. In this case, choose Origin Zip from the delivery route dataset and postcode from the GeoPostcodes dataset.

You should see seventeen rows matched after the merge action at the bottom of the image with a green tick mark. Click OK once the merge is done.

The next screen indicates the GeoPostcodes dataset has been merged as a last column with the delivery route dataset. However, the merged dataset appears as a single table field instead of as individual fields from that dataset. You cannot use the fields from this structure as is for visualization, so you need to convert this table-field structure into a compatible field-level format.

Click on the convert icon available on the right side of the GeoPostcodes dataset, as shown above. In the window, uncheck both the postcode column and Use original column name as prefix, and then click OK. You will now see the merged delivery route dataset containing the latitude and longitude fields from the GeoPostcodes dataset as individual fields.

Either right-click or double-click on the latitude column and select the Rename option to update the name to Origin Latitude. Then, rename the longitude column to Origin Longitude.

Repeat all steps covered so far in this merge section to merge the present delivery route dataset with the GeoPostcodes dataset. Keep in mind that, this time, the merge must be based on the Dest Zip field from the delivery route dataset and postcode from the GeoPostcodes dataset. Finally, rename the resultant latitude and longitude columns to Dest Latitude and Dest Longitude, respectively.

Once you’re done, your dataset should match the one below:

Click Close & Apply to close the Power Query Editor window and return to the Power BI interface:

On the right side, you can see the delivery route dataset containing the newly merged fields from the GeoPostcodes dataset. Click the Save icon to save your work so far.

Creating Visualizations

Now that your dataset has been prepared, you’re ready to create visualizations.

Flow Map of Delivery Routes and Destinations

First, you’ll create a map visualization to show delivery routes and destinations. Start by clicking the three-dot icon in the Visualizations panel and choose Get more visuals:

The next window allows you to download various visuals:

Search for flow map and click on the visual shown below to see details about this flow map. Click Add to add the visual to your visualizations panel.

Once the visual is added, click OK in the import message-response dialog box. You will now see this flow map’s visual icon in the visualizations panel:

Click on the Flow map icon to start creating a report:

From the Data panel, select the fields Origin City, followed by Dest City in the delivery_routes_data dataset. This will update the Flow map visualization as follows:

Next, drag the corners of the visual using the mouse to fill half the chart area:

Move your mouse over any of the routes to see the source and destination location details in the flow map visual.

Although you can see a map with routes connecting the source and destination, the location points of the source and destination marked on the map would not be accurate because it is based on a city rather than a location. To make it accurate, you’ll have to use the latitude and longitude information from GeoPostcodes. Let’s see the difference in map visualization once you have applied these data points from GeoPostcodes.

Click (don’t check the box) on the Dest latitude field in the Data panel of delivery_routes_data:

A new toolbar related to the selected field appears below the top menu bar. Its summarization drop-down box is selected as Sum. Click and change the value to Average since the average of the same value against the total number of records will return the value itself, whereas the sum aggregation type would not be meaningful in this case.

Repeat the same step for the Dest Longitude, Origin Latitude, and Origin Longitude fields. Then, drag and drop all four of these fields into the respective options of the flow map, as shown below:

You now have a map visualization with accurate location data.

Bar Chart of Performance Metrics

Next, let’s create a bar chart that showcases performance metrics like total delivery time by each destination city. The chart will also include additional information, such as the number of orders split by an on-time flag, which will give insight into how many orders get delivered on time and how many are not.

Click on the empty space of the reporting area and click on the visual Stacked bar chart as shown:

From the **Data ** panel, select the fields in the delivery_routes_data dataset in the following order:

1. Delivery Time (hours)
2. Dest City
3. On-Time
4. Order Id

This will update the Stacked Bar chart as follows:

Hover your mouse over the bar chart to see additional information, such as the number of orders delivered and whether they were delivered on time.

Line Chart of Route Efficiency

After completing the bar chart, adjust the visual to fill only 50 percent of the second vertical half of the reporting area.

You will use the remaining space to create a line chart showing route efficiency. Click on this empty space and then click on the line chart visual:

From the Data panel, select the following fields in the delivery_routes_data dataset in this order:

1. Miles
2. Ship Date

This will update the line chart as follows:

To update the line chart with the delivery time details, drag the Delivery Time (hours) field from the Data panel to the secondary axis input in the Visualizations panel.

This line chart allows you to identify trends and patterns in route efficiency. For example, you might see if shorter routes are taking disproportionately more delivery time. You can also compare different periods to see if efficiency is improving or deteriorating.

Applying Filters on the Dashboard

You can also use the filters panel to apply data filters on the entire dashboard.

To do this, select the entire dashboard by pressing Ctrl+A (Windows) or Command-A on your keyboard. This activates the filters for the entire page or dashboard. Expand the filter panel using the toggle expand icon, as shown:

Next, drag and drop the field Ship Date from the Data panel to the Filters on this page section of the Filters panel.

Select any dates of your choice from the Ship Date filters to apply the selected data points as filters on the dashboard.

You will see the dashboard showing data only for those particular shipping dates.

If you want to save your work, remember to click the save icon, as mentioned earlier.

Conclusion

Congratulations! You now know how to build a robust logistics dashboard in Power BI. You also saw how GeoPostcodes’ precise latitude and longitude data enhances the accuracy of location-based insights in PowerBI, which allows you to accurately plot delivery routes and destinations on map visualizations.

Curious about what else you can do with GeoPostcodes’ comprehensive international postal code databases? Be sure to check out these articles:

• “Address Validation in Salesforce Sales Cloud: An In-Depth Guide”
• “Using Address Validation in Microsoft Dynamics 365”
• “How to Add Geocoded Zip Codes to Salesforce”

FAQ