Inventory Dashboard
In this section, you’ll learn how to turn raw data into an interactive Power BI report. We’ll cover everything you need to know to create a complete project from start to finish.
The Inventory Dashboard shows key information like stock levels, goods received, and stock issued for sales orders. It helps you monitor inventory movements, track available quantities, and manage costs effectively. You can easily view item details, locations, and transaction history to ensure smooth inventory operations. Try it yourself first! Build the dashboard yourself and, if you get stuck, refer to the video for help.
1-Connect Power BI to your data source, download this Excel
2-Understand the Tables of your data set, we have Seven tables in the data set:
• Grn_Transaction: This table tracks goods received in inventory. It includes details like purchase order numbers, vendor names, shipment quantities, receipt dates, and item prices. It helps monitor inventory updates and receive goods efficiently.
• Inventory_Organization: This table provides details about organizations, including their unique IDs, codes, names, and regions. It helps identify and manage organizational locations for inventory and operations.
• Inventory_Transaction_Type: This table defines different transaction types with a unique ID, name, and description. It helps categorize and explain various types of transactions for better clarity and tracking in the system.
• Inventory_Transaction: This table records transaction information, including quantities, costs, and types. It tracks units of measure, organization IDs, sub-inventory codes, and item costs. It helps monitor inventory movements, transaction types, and updates for better inventory management.
• Item_Master: This table contains detailed information about items in inventory, such as item codes, descriptions, category codes, brand details, and product types. It also tracks stock status, unit of measure, creation, and update dates. Organizational details like ID, name, and region help identify where the items are managed, enabling efficient inventory tracking and categorization.
• OnHand_Qty: This table shows the available stock for items. It includes item IDs, quantities, costs, inventory value, lot numbers, sub-inventory details, and storage locations. It helps track how much stock is available and where it is stored.
• SO_Issue: This table tracks stock issued for sales orders. It includes details like transaction quantity, item cost, sub-inventory, and item IDs. It also records organization information (ID, code, name, region) and helps monitor inventory movements related to sales orders in the inventory dashboard.
3-Build relationships between different tables to show data connections.
1. Grn_Transaction to Item_Master
• Join Type: Many-to-One
• Key: Inventory_Item_Id
• Description: This connection links inventory transactions to the item master, enabling tracking of item details such as cost, descriptions, and categories for each inventory movement.
2. Inventory_Transaction to Item_Master
• Join Type: Many-to-One
• Key: Inventory_Item_Id
• Description: This connection links inventory transactions to the item master, enabling tracking of item details such as cost, descriptions, and categories for each inventory movement.
3. Inventory_Transaction to Inventory_Transaction_Type
• Join Type: Many-to-One
• Key: Transaction_Type_ID
• Description: This relationship links inventory transactions to their respective transaction types, providing clear identification of transaction purposes, such as receipts, issues, or adjustments.
4. OnHand_Qty to Item_Master
• Join Type: Many-to-One
• Key: Inventory_Item_Id
• Description: This relationship connects on-hand stock quantities to the item master, enabling details like item descriptions, units of measure, and categories to be linked to available stock.
5. OnHand_Qty to Inventory_Organization
• Join Type: Many-to-One
• Key: Organization_Id
• Description: This connection links on-hand stock quantities to inventory organizations, helping to track stock availability by specific locations or warehouses.
6. SO_Issue to Item_Master
• Join Type: Many-to-One
• Key: Inventory_Item_Id
• Description: This relationship links stock issued for sales orders to the item master, allowing item details such as descriptions, units of measure, and costs to be tracked for issued inventory.
4- Create Visualisations to display important information in charts, tables, and maps. Your dashboard should match the below layout.
