Sales 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. By the end of this topic, you’ll be able to create a complete dashboard:
1. Connect Power BI to your data source, download this Excel Sales dataset.
2. Understand Tables of your data set, we have seven tables in data set
a. Sales order table: This table contains the details of each sales order placed by a customer. It typically includes:
• Order ID: Unique identifier for each sales order.
• Customer ID: Identifier for the customer who placed the order.
• Order Date: Date the order was placed.
• Total Amount: Total value of the order.
• Order Status: Status of the order (e.g., pending, shipped, delivered
b. sales order line table: This table stores the data order, showing the specific products or services sold within each order.
• Order Line ID: Unique identifier for each line item within an order.
• Order ID: Foreign key linking to the Sales Orders Table.
• Product id: ID for the product being sold.
• Quantity: Number of units sold.
• Unit Price: Price per unit for the product.
• Line total: Total value of the line item
c. Salesman Table: This table includes information about the customers making the purchases.
• Customer ID: Unique identifier for each customer.
• Customer Name: Name of the customer (individual or company).
• Customer Type: Type of customer (e.g., retail, wholesale).
• Location: Location or address of the customer.
• Contact Info: Phone number, email, etc.
d. Order Item Table: The Order Item table stores details about the specific products or services included in each order.
• Order ID: Foreign key linking this item to the Order or Order Header table. This ties the item to the main order.
• Product ID: Unique identifier for the product or service being sold. It links to the Products table where detailed information about the product (e.g., name, category, price) can be retrieved.
e. order Type Table: The Order Type table stores information about the different types or categories of orders that can exist in the system.
• Order Type Name: A descriptive name for the order type (e.g., “Retail Order,” “Wholesale Order,” “Custom Order”).
• Description: A more detailed description of what the order type represents (e.g., “Orders placed for retail customers”).
• Order Type ID: A unique identifier for each type of order (e.g., “Standard,” “Rush,” “Backorder,” “Special Order”).
f. Sales Organization (Sales Org): table is a key table in a sales and distribution (SD) system, especially in ERP systems like SAP or other enterprise systems.
• Sales org id: A unique identifier for the sales organization. This is usually a numeric or alphanumeric code that identifies each sales organization in the system.
• Sales office id: Some systems link the sales organization to a specific sales office or location. This ID helps to further break down sales operations at the office or location level.
• Country/region: Some sales organizations are defined by geographic regions. This column could specify the country or region where the sales organization operates (e.g., “USA,” “Germany,” “Asia Pacific”).
3. Build relationships between different tables to show data connections
• Sales Order Table to Sales Order Line Table:
o Join Type: Many-to-One
o Key: Sales Order ID
o Description: Multiple order in the sales order table will have single order id in the sales order line table.
• Sales Order line Table to Sales Order item Table:
o Join Type: Many-to-Many
o Key: Sales Order Inventory Item ID
o Description: A Sales Order can have many products, and a Product can appear in many orders.
• Sales Order Table to Sales Org Table:
o Join Type: Many-to-One
o Key: Sales Order User ID
o Description: A Sales Order belongs to one Sales Organization.
• Sales Order Table to Sales Order Salesman Table:
o Join Type: One-to-Many
o Key: Sales rep id
• Description: A single Salesman can handle multiple Sales Orders, but each Sales Order is assigned to one Salesman
• Sales Order Table to Sales Order type Table:
o Join Type: One-To-Many
o Key: Sales order type id
o Description: A single Order Type can be associated with many Sales Orders, but each Sales Order belongs to one Order Type.
• Sales Order Table to Sales data dim Table:
o Join Type: One-To-Many
o Key: year
o Description: A Sales Order links to one Dim Date; a Dim Date can have many Sales Orders).
- Create visualizationsto display important information in charts, tables, and maps.
Your dashboard should match below layout.
Sales dashboard
