Enhancing Supply Chain Efficiency with Power BI: A DataCo Case Study – Part 2
Table of Contents
Introduction
In Part 1 of our case study, we explored how DataCo tackled delivery inefficiencies using Power BI. Now, we’ll dig deep into using Power BI to analyse sales and profitability for smarter supply chain decisions.
We will utilize key sales and profitability information and trends and identify inefficiencies with the help of a Power BI Report.
It is quite complex to manage a supply chain. Focusing on sales, knowing customer behaviors, and achieving profits aren’t easy to analyze all at once. The problems that many organizations encounter in boosting their sales and controlling their expenses while handling the matters of the supply chain are typical for many businesses.
With Power BI, you can transform that confusion into a clear understanding for your supply chain data. This powerful tool provides the insights you need to make informed decisions and drive your business forward.
By visualizing key performance indicators such as:
- Sales & Profit by Customer Segment Over Time
- Hourly Sales
- Top 5 and Bottom 5 Products by Sales
- Sales by Order Region
- Sales per Customer
- Benefit per Order
- Market Performance
you can gain a deep understanding of what is driving your revenue and what is holding you back.
Let us get started and explore how DataCo used Power BI to unlock its sales potential!
Understanding the Data
For this case study, we will be using the DataCo Supply Chain data set, which clearly depicts various aspects of supply chain processes such as procurement, manufacturing, marketing, sales, profit, and distribution.
Note: You can download the dataset from the download section given at the end of the blog.
Overview of the DataCo Supply Chain Dataset
Here is a breakdown of the critical components:
- DataCoSupplyChainDataset.csv
The Excel file contains 53 numbers of columns and 180520 numbers of rows. The rows exhibit a specific client’s order while the column portrays traits with regards to the order, the number of sales by the customer, status, and types of products offered. - DescriptionDataCoSupplyChain.csv
The purpose of this file is to provide specifications pertaining to the core data set that concerns the attributes.
Key Data Points for Sales and Profit Analysis
To effectively analyze sales and profitability, we have incorporated the following columns in addition to the dataset from Part 1 of this case study:
- Sales per Customer: This metric measures the average sales generated per customer segment, giving insights into customer spending behavior.
- Benefit per Order: This reflects the average profit margin per order, a crucial measure of financial performance.
- Market: The market segment column breaks down sales performance across global regions such as Europe, USCA, and Africa.
- Order Region: This field shows the geographical origin of sales orders, providing insights into regional performance.
- Department Name: Helps track product category performance, including Fan Shop, Apparel, Golf, and more.
- Customer Segment: Categorizes customers into Consumer, Corporate, and Home Office segments, allowing for a more granular analysis of customer behavior.
- Order Item Quantity: Captures the number of units purchased in each order, essential for understanding order volume.
- Product Price: Indicates the price of each item.
- Product Name: Identifies individual products in the dataset, which helps in tracking top and bottom performers.
- Sales: Represents the total amount generated from the sale of products in each order.
These data points will be central to generating comprehensive visualizations that track sales and profit trends, which will, in turn, guide better decision-making for the business.
Next, we move this data to Power BI and then transform it.
Implementing Power BI for Supply Chain Efficiency
Now that we have a solid understanding of the dataset let us dive into implementing Power BI to analyze and optimize supply chain efficiency.
Step 1: Transform Data
To begin, we will load the dataset into Power BI and clean it for analysis.
Key data preparation steps include:
1.) All capital feature values are represented in dollars and up to 2 decimal places.
2.) It’s essential to delve into more complex data transformation techniques, such as converting date time format to hourly format. This skill will be handy for our hourly order sales visualization in this blog. To learn these complex data transformations, register for our Power BI Advance
Step 2: Create Calculated Columns and Measures
For accurate sales and profit insights, we’ll calculate revenue, profit, and rankings of top and bottom products.
Calculated column:
Revenue:
Revenue = ‘Order'[Order Item Quantity] * ‘Order'[Product Price]
Total Cost = ‘Order'[Order Item Quantity] * ‘Order'[Product Price]
Total Sales Total Sales = SUM(‘Order'[Sales])
Profit = SUM(‘Order'[Revenue]) – SUM (‘Order'[Total Cost])
Top5Products = IF ( RANKX (ALL (‘Order'[Product Name]), [Total Sales],, DESC) <= 5, [Total Sales], BLANK () )
Bottom 5 Products
Bottom5Products =
IF (
RANKX (ALL (‘Order'[Product Name]), [Total Sales],, ASC) <= 5,
[Total Sales],
BLANK ()
)
Want to rank your products in Column or Bar Charts? Learn with our instructors in the Power BI DAX course to create a DAX measure for ranking products!
You can also download the exercise file to access the complete dashboard at the end of this blog.
Step 3: Building the Sales & Profit Dashboard
Let us look at the key visuals that provide insights into the sales and profitability aspects of the supply chain:
1. Sales & Profit by Customer Segment Over Time (Line and Stacked Column chart)
This visual combines a stacked column chart for a count of customer types, i.e. Consumer, Corporate and Home Office and a line for sales and profit. The visual helps you monitor the sales volume and profitability trends across different years.
Steps to Create:
- Place Date Hierarchy on the X-axis.
- Sales and Profit on the Line Y-axis, and Customer Segment on Column Legend and Column Y-axis.
Interpretation:
This visual helps you understand which customer segments are driving the most sales and profit. For example, while the Consumer and Corporate segments may show steady sales, the Home Office segment might reveal declining profits.
Insights like these can drive changes in marketing strategies and customer service to optimize profits across segments.
2. Sales by Order Region (ArcGIS Map Visualization)
This ArcGIS map visualization shows sales distribution across global order regions. Circle markers represent the size of sales from regions such as Western Europe, Southeast Asia, or South Africa.
Steps to Create:
- Use Order Region for location data and Sales to size the markers.
- To proportion of sales in each region, place Sales and Order Region in tooltips.
Learn to format and enable Azure Maps in Power BI in our Azure Map Blog.
Interpretation:
This visual helps identify top-performing geographic areas. For instance, you might discover that sales in Western Europe are significantly higher than in Southeast Asia, prompting you to explore potential growth strategies in underperforming regions.
3. Top 5 and Bottom 5 Products by Sales (Column Chart)
A Column chart showing the top 5 and bottom 5 products by sales gives a clear picture of which products are driving revenue and which are underperforming.
Steps to Create:
- Plot Product Name on the X-axis and Top5Products & Bottom5Products on the Y-axis.
- Use RANKX() DAX function to create ProductRank DAX Measure. To learn how to create a DAX measure for ranking products, register for our Power BI DAX
- Use ProductRank in the tooltips to display the top 5 products in ascending and bottom 5 products in descending order in single column chart.
If you want to format your visuals like a pro, just as we’ve done here, then register for our Power BI Visualization Course! You’ll learn how to create visually engaging, professional-quality dashboards that convey insights effectively, making your data analysis more impactful and accessible.
Interpretation:
This visual highlight the best and worst-performing products. For example, a product like Field & Stream Sports may generate significant revenue, whereas CDs of Rock may consistently underperform. This insight helps make decisions on product optimization or discontinuation.
4. Sales by Department Name (Bar Chart)
Interpretation:
This visual is critical for understanding departmental performance. For instance, Fan Shop may dominate sales, but you could notice that smaller departments, like Book Shop, need strategic adjustments to improve performance.
This could lead to decisions around pricing strategies, inventory management, or promotions.
5. Sales by Market (Pie Chart)
This pie chart breaks down sales across different global Markets, such as Europe, LATAM (Latin America), Pacific Asia, Africa, and USCA (United States and Canada). It provides a clear visualization of each region’s market share in terms of overall sales.
Steps to Create:
- Use Market for the pie slices and Sales for the values.
Interpretation:
This pie chart offers a clear snapshot of market share distribution. For instance, Europe accounts for the most significant slice of sales, while Africa and LATAM contribute smaller portions.
These insights are vital for prioritizing market development and understanding where there may be potential for growth.
6. Hourly Sales (Area Chart)
An area chart tracks hourly sales patterns across multiple years, highlighting peak sales hours and showing sales trends over a 24-hour period. We have also used order time to find out which time the customers are most and least active and, hence, which times can be focused on to improve the growth of the company.
The Hourly Sales area chart tracks sales trends across different times of the day for multiple years (e.g., 2015–2018). This visual helps identify peak hours for customer orders, enabling better operational planning, like staffing or delivery scheduling.
Steps to Create:
- Use Hour on the X-axis and Sales on the Y-axis.
- Grouped them by year by adding Year to the legend field to show sales patterns over different hours of the day.
Learn how to add sliders in the Power BI visual, as shown in this area chart, by registering for our Power BI Visual course.
You can also download the exercise file to access the complete dashboard at the end of this blog.
Interpretation:
This visual shows when customers are most active. For example, sales may peak between 10:00 AM and 2:00 PM, allowing DataCo to allocate resources accordingly to support these busy periods.
On the flip side, if sales tend to drop in the evening hours, DataCo can plan cost-saving strategies like adjusting staffing levels during those times.
7. Sales by Customer Segment (Tree Map)
The Tree Map visual provides a clear breakdown of total sales by different Customer Segments—consumer, Corporate, and Home Office. Each segment is represented by a distinct block, the size of which corresponds to the volume of sales generated by that segment.
Steps to Create:
- Use Customer Segment as the category and Sales as the value.
- Assign different colors to each segment for better differentiation.
Interpretation:
This visual helps you understand which segments are central to overall sales. For instance, the Consumer segment prevails in the chart, whereas the Corporate and Home Office segments are potentially problematic.
8. KPI Cards (Key Performance Indicators)
The KPI Cards objectively show metrics of the performance of the supply chain. Each card highlights a crucial statistic that offers a snapshot of overall performance:
- Average of Benefit per Order: $21.97
This metric measures the average profit or “benefit” per order, which gives the profitability per order.
- Sum of Sales: $36.78M
This metric indicates the total sales value recorded on all the orders, bringing revenue to the company.
- Average of Sales per Customer: $183.11
It exposes the ability to generate sales per client, making it easier to comprehend client worth and consumption habits.
Interpretation:
These KPIs are useful because they allow one to gain an instantaneous snapshot of the firm’s financial status. These measures play a significant role in helping DataCo determine the company’s overall profitability, revenue, and customer spending pattern.
Additional Insights from the Dashboard
Beyond the visualizations and calculations, Power BI’s interactive features allow you to drill deeper into the data and uncover hidden trends:
- Filtering Capabilities:
Users can filter by time periods (e.g., Year), customer segments, regions, and product categories to compare sales and profit performance across multiple dimensions.
- Cross-Visual Filtering:
The interactive dashboard allows users to click on one visual (e.g., a specific market in the pie chart) to filter all other visuals. For example, selecting Europe in the Sales by Market pie chart will update all other visuals to show sales, profit, and product performance within that region.
Key Improvements and Results
By using Power BI to visualize and analyze their sales and profit data, DataCo achieved:
- Improved decision-making through data-driven insights into customer segments, product performance, and market regions.
- Higher revenue and profitability by identifying and addressing underperforming areas and focusing on high-value products and customer segments.
- Operational efficiency improvements by leveraging insights from hourly sales data and regional performance analysis.
These changes have positioned DataCo for continued growth and long-term success in their supply chain management.
Take the first step towards a smarter supply chain by integrating Power BI into your data analysis toolkit. With the right approach, you can turn data challenges into opportunities for growth and optimization.
Complete Dashboard
In the downloadable Power BI file linked below, you will find a comprehensive dashboard that includes all the visualizations that we created above. This is an interactive dashboard where you can perform slice-and-dice or drill-down operations!
Sign up today for our Power BI Report Design Course and start creating stunning Power BI visuals just like the sample above! You’ll learn how to design professional-grade reports that are not only visually appealing but also deliver impactful insights.
Downloads
Stay tuned for more insights and actionable steps for enhancing your supply chain with Power BI!
Conclusion
In the second part of this case study, we worked with sales and profitability data; we described how DataCo used Power BI in their supply chain for sales & profit.
Creating compelling reports with visuals such as Sales per Customer, Profit by Region, Sales by Market, Top and Bottom Products, and Sales & Profit Trend by Customer Segment can help organizations understand the selling aspects and shortcomings and, therefore, enhance their decisions and activities.
This practical application of Power BI will give you the confidence to tackle real-world data challenges.
What’s Next?
In Part 3, we will elaborate more on Power BI for advanced analytics such as predictive modeling and inventory optimization. This will help you stay ahead of market trends and refine your supply chain operations for continued growth.
Further Learning and Resources
Are you a data analyst and want to learn more about Power BI?
- Power BI training in Australia: We offer comprehensive training sessions across various cities, including Sydney, Melbourne, Brisbane, and Canberra.
- Power BI Crash Course eBook: Download our detailed guide to take your Power BI expertise to the next level, covering topics from basics to advance with practical examples and step-by-step instructions. (Limited time offer)
Ready to enhance your Power BI skills? Sign up for our training courses and explore our wide range of resources designed to elevate your data analytics capabilities.
Contact Us: Make an enquiry about our Power BI courses and find the right training for you.
Related Articles
- Common Mistakes When You’re Working with Spatial Data in Power BI
- Finding the trend line in your data using Advanced analytics in Power BI
- Creating Visuals with Q&A
Are you a data analyst and want to learn more about Power BI?
Why not sign up for Power BI training in Australia. We provide our services in the following regions:
In-person Power BI Training
We offer comprehensive Power BI training across Australia, tailored to suit different learning styles and proficiency levels. We deliver all our training in collaboration with Dynamic Web Training.
