An example Power BI report for a medium-sized logistics and freight forwarding company. I tried to illustrate the most relevant information from the perspective of managing such a company, while also selecting unusual visualizations that are different from the other reports. The report is essentially a single-page operational dashboard focused around a map of the routes used during the reported period. The greater the density on a given route, the more shipments there were historically between those locations. Simultaneously, the banner above the map shows the most popular destinations (ports, airports, terminals), revenue for that route, and changes compared to the previous month. 

All visualizations can, of course, be filtered by the means of transport used (road, rail, air, sea) and by selected months. The second visualization worth noting is the waterfall bar chart at the bottom of the dashboard, showing how individual means of transport contributed to operating profit month by month. Here we can see how, for example, almost the entire profit in May 2020 was then consumed by the loss on sea shipments in June 2020. The smallest visualization is a dynamic map of the container sizes used to handle the shipment.   

In this report, I wanted to show how interesting it can be to present industry data coming from various systems (accounting, CRM, shipment tracking). I tried to emphasize the significance of geolocation data in reports for the logistics and freight forwarding industry. Unfortunately, the report is based on historical data, but one can imagine expanding it based on data streaming and real-time shipment tracking.  

I used the publicly available Shipments dataset from the "Power BI Challenge Transport & Shipping Data" available at https://forum.enterprisedna.co/t/power-bi-challenge-12-transport-shipping-data/14846 In its physical form, it was an Excel file with over 100 columns and 200 rows, full of errors and inconsistencies. I downloaded the 5-letter UNLOCO location codes for countries and regions/cities from https://unece.org/trade/cefact/unlocode-code-list-country-and-territory in CSV format. 

Power Query (M) – data transformations: merging, filtering, unpivot, adding indexes; Azure Maps – route visualization as a path layer, Power BI Desktop (main environment), Power BI Service (management) 

The biggest challenges resulted from the low quality of the data, the large size of the source file (>100 columns), and inconsistent and incomplete records. The result was labor-intensive normalization and transformations. This led to a very extensive data model in Power BI. The route map visualization required familiarization with the Azure Maps documentation and performing transformations of the Origins, Destinations, and Unlocodes tables to meet the requirements of this visualization (path_id and point_order columns). AI Copilot proved very helpful in preparing the script in the M language. 

The UNLOCO source in CSV format with country and city codes was also a problem due to its size (over 300,000 rows) and a format that hindered the Power BI CSV connector (an irregular summary row appearing for each country in the file caused null values). The visualization showing the container sizes used also presented considerable difficulties, requiring an unpivot of several columns with sizes (20F, 30F, etc.) and grouping by ShipmentID. Some time intelligence DAX measures required prior expansion of the already large Shipments fact table with a calculated column. As a result, we could track KPIs such as the % of shipments delivered on time. Example photos from work on the report below: