Building an Inventory Analysis Dashboard in Power BI
Table of Contents
Introduction
Effective inventory management is essential for business growth. The lack of effective inventory management results in stock-out situations, having too much stock, higher storage costs, and losing sales opportunities. To overcome these obstacles, companies resort to data analytics tools such as Power BI to make raw inventory data useful for managers and stakeholders.
For further information about supply chain and inventory analytics in Power BI, you may refer to the DataCo supply chain case study published by Power BI Training, which demonstrates how data-driven dashboards can support operational decision-making.
In this blog post, we will create an end-to-end Inventory Analysis Dashboard in Power BI using Star Schema data modeling, Power Query transformation, and DAX calculations.
Note: For reference and practice, the exercise file can be downloaded from the link provided below.
What is Inventory Analysis?
The inventory analysis is a process that involves analyzing inventories, demands, and supply chain of companies to improve their decision making and effectiveness.
Using Power BI allows you to:
- Conduct real-time inventory analysis.
- Recognize out-of-stock items.
- Recognize poor-performing inventories.
- Analyze demands and supplies trends.
- Save inventory maintenance costs.
- Enhance warehouse and supplier performance.
Dataset Overview
For this project, the dataset includes key supply chain attributes:
· Date
· SKU ID
· Warehouse ID
· Supplier ID
· Region
· Units Sold
· Inventory Level
· Supplier Lead Time (days)
· Reorder Point
· Order Quantity
· Unit Cost
· Unit Price
· Promotion Flag
· Stockout Flag
· Demand Forecast
This dataset supports both operational reporting and predictive inventory analysis.
Data Preparation in Power Query
Data transformation includes the following steps:
- Remove the duplicate entries.
- Data correction (Date, Numeric, Text).
- Renaming columns for consistency.
- Handling null or missing values.
- Standardize date formats.
- Validating inventory and sales data.
A well-prepared dataset improves report accuracy and overall performance.
Star Schema Data Modeling (Best Practice)
To achieve scalability and efficiency, we will develop a Star Schema design. For a detailed explanation of Star Schema design principles and best practices, refer to the article Schemas in a Data Model published by Power BI Training.
Fact Table
Fact Inventory
Contains transactional and measurable data:
- Date (FK)
- SKU ID (FK)
- Warehouse ID (FK)
- Supplier ID (FK)
- Units Sold
- Inventory Level
- Demand Forecast
- Unit Cost
- Unit Price
- Reorder Point
- Order Quantity
- Promotion Flag
- Stock out Flag
*FK (Foreign key)
Dimension Tables
Dim Product (SKU Master)
- SKU ID.
- Product attributes (if available).
Dim Warehouse
- Warehouse ID.
- Region.
Dim Supplier
- Supplier ID.
- Supplier Lead Time.
Dim Date
- Date.
- Year.
- Month.
- Quarter.
Relationships
- Dim Product → Fact Inventory (1:*)
- Dim Warehouse → Fact Inventory (1:*)
- Dim Supplier → Fact Inventory (1:*)
- Dim Date → Fact Inventory (1:*)
Key DAX Measures
The list below shows the KPIs that are included in the dashboard for the business purposes:
Total Inventory
Total Inventory =
SUM ( Fact Inventory[Inventory_Level] )
Total Units Sold
Total Units Sold =
SUM ( Fact Inventory[Units_Sold] )
Revenue
Revenue =
SUMX ( Fact Inventory, Fact Inventory[Units_Sold] * Fact Inventory[Unit_Price] )
Inventory Turnover Ratio
Inventory Turnover =
DIVIDE ( [Total Units Sold], AVERAGE ( Fact Inventory[Inventory_Level] ) )
Stock Status (Low Stock Indicator)
Stock Status =
IF (
SUM ( Fact Inventory[Inventory Level] ) < MAX ( Fact Inventory[Reorder Point] ),
“Low Stock”,
“OK”
)
Demand vs Supply Gap
Demand Supply Gap =
SUM ( Fact Inventory[Demand Forecast] ) – SUM ( Fact Inventory[Units Sold] )
Dashboard Visualizations
The Power BI dashboard includes the following insights:
KPI Cards
- Total Inventory.
- Revenue.
- Stock out Status.
- Inventory Turnover.
Trend Analysis
- Inventory movement over time.
- Top and Low Performing SKUs.
Key Business Insights
The following dashboard will help businesses determine:
- Which products are at risk of stock outs?
- Where is overstocking occurring?
- Which SKUs are performing best and worst?
- How are inventory levels changing over time?
- What is the current inventory turnover ratio?
Performance Optimization Best Practices
To attain scalable performance, it is important to follow some best practices:
- Use Star Schema Design.
- Use Measures instead of Calculated Fields.
- Discard Auto Date & Time feature in Power BI.
- Avoid adding unnecessary fields in Fact tables.
- Optimize relationships for single-direction filtering.
Conclusion
Analysis of Inventory through Power BI involves changing supply chain data into business intelligence data. By creating an effective data model, DAX measures, and dashboards, a company will be able to operate effectively, save costs on inventory, and make sound decisions in business.
FAQs
Power BI helps the company monitor inventory in real time, reduce stock outs and over stocking, forecasting, manage the warehouse, and make decisions based on the data.
Star Schema is the name given to a method of organizing data where data is structured in a particular schema that has one fact table and several dimension tables.
Extended Learning & References
For more in-depth learning and advanced Power BI training, enroll in
- Microsoft 365 Copilot Training Courses – Practical Learning for Modern Work
- Power BI Advanced Training Course.
- Power BI Report Design
- Power BI Essentials
- Power BI DAX Essentials
- Power BI Service Course
Here, you can access detailed courses and resources designed to enhance your Power BI skills and take your reporting capabilities to the next level.
Downloads
The dashboard uses a Star Schema along with DAX measures for inventory turnover and stock status. How would you adapt the model if inventory data came from multiple warehouses with different reorder rules?
If inventory data comes from multiple warehouses, I will modify the Star Schema by adding another dimension named ‘Warehouse’. The main purpose of such a modification would be the possibility to analyze stock availability and its turnover for each warehouse separately. It is important to note that I will keep a separate table for reorder policies because each warehouse has its own minimum stocks. This would allow me to use DAX measures to calculate stock status and identify which products need replenishment at each warehouse.