Project Overview: Sales and Returns Performance Dashboard for a retail business.
Key Objectives:
• Sales Order Analysis: Analyze sales performance across different regions, products, and time periods.
• Returns Analysis: Identify which products or categories have higher return rates and their impact on revenue.
• Employee Performance: Link sales and returns to specific employees to assess performance.
Data Model: Relationships among tables
1. Load the Data into Power BI
• Open Power BI Desktop.
• Go to Home > Get Data > Excel and select your [Link] file.
• Load the three sheets: Orders, Returns, and People.
2. Create Relationships between Tables
• After loading the data, go to the Model View (icon on the left).
• Create relationships:
o Orders → Returns: Create a relationship between the Order ID in the Orders table and the Order ID in the Returns table.
o Orders → People: Create a relationship between the Region (or another common field) in the Orders table and the Region (or Employee ID, if available)
in the People table.
3. Set Up Measures Using DAX
Create a new table to have all the measures.
Measures Table = {1}
Sales Analysis DAX
1. Total Sales: Go to the Measures Table. Right-click on the table and select New Measure.
Total Sales = SUM(Orders[Sales])
2. Total Profit: Follow the same steps to create a new measure for Total Profit:
Total Profit = SUM(Orders[Profit])
3. Monthly Sales Trend: Create a measure for monthly sales:
Monthly Sales = CALCULATE(SUM(Orders[Sales]), DATESMTD(Orders[Order Date]))
4. Profit margin as a percentage of sales: Create a measure for profit margin.
Profit Margin = DIVIDE(SUM(Orders[Profit]), SUM(Orders[Sales]))
Returns Analysis DAX
1. Total Returns: In the Measures table, create a new measure for counting total returns:
Total Returns = COUNTROWS(Returns)
2. Return Rate: Create a measure for return rate:
Return Rate = DIVIDE(COUNTROWS(Returns), COUNTROWS(Orders))
3. Returned Sales: Create a measure to calculate the sales of returned orders:
Returned Sales = CALCULATE(SUM(Orders[Sales]), Orders[Order ID] IN VALUES(Returns[Order ID]))
4. Lost Profit Due to Returns: Create a measure to calculate the lost profit:
Lost Profit Due to Returns = CALCULATE(SUM(Orders[Profit]), Orders[Order ID] IN VALUES(Returns[Order ID]))
Employee Performance DAX
1. Sales by Employee: In the Measures table, create a measure for sales by employee:
Sales by Employee = SUMX(RELATEDTABLE(Orders), Orders[Sales])
2. Return Rate by Employee: Create a measure for return rate per employee:
Return Rate by Employee = DIVIDE(COUNTROWS(RELATEDTABLE(Returns)), COUNTROWS(RELATEDTABLE(Orders)))
3. Employee Contribution to Profit: This calculates the profit contributed by each employee:
Employee Profit Contribution = SUMX(RELATEDTABLE(Orders), Orders[Profit])
4. Set Up Visualizations in Power BI
1. Sales Dashboard Visuals
• Visual Type: Card
o Drag the Total Sales measure to the Fields.
• Visual Type: Card
o Drag the Total Profit measure to the Fields.
• Visual Type: Card
o Drag the Monthly Sales measure to the Fields.
Change the currency to riyal Omani.
Total Sales by Region
• Visual Type: Bar Chart (Clustered Bar Chart)
o Y-Axis: Drag the Region field from the Orders table.
o X-Axis: Drag the Total Sales measure.
Sales by Product Category
• Visual Type: Bar Chart (Stacked Bar Chart)
o Y-Axis: Drag the Category field from the Orders table.
o X-Axis: Drag the Total Sales measure.
Monthly Sales Trend
• Visual Type: Line Chart
o X-Axis: Drag the Order Date field. Power BI will automatically create a date hierarchy (Year → Quarter → Month).
o Y-Axis: Drag the Monthly Sales measure.
Profit Margin and Total Profit by Year
• Visual Type: Line Chart
o X-Axis: Drag the Order Date (with only Year hierarchy).
o Y-Axis: Drag the Profit Margin and Total Profit measure.
2. Returns Dashboard Visuals
Total Returns
• Visual Type: Card
o Drag the Total Returns measure to the Fields.
Return Rate
• Visual Type: Gauge or Card
o Drag the Return Rate measure to the Fields.
Lost Profit Due to Returns
• Visual Type: Card
o Drag the Lost Profit Due to Returns measure to the Fields.
Returned Sales by Product Category
• Visual Type: Line Chart
o X-axis: Drag the Category field from the Orders table.
o Y-axis: Drag the Returned Sales measure.
Return Rate by Region
• Visual Type: Map
o Location: Drag the Region field.
o Bubble size: Drag the Return Rate measure.
Shipment Mode
• Visual Type: Slicer
o Drag the Ship Mode into the Field.
3. Employee Performance Dashboard Visuals
Sales by Employee
• Visual Type: Matrix
o Rows: Drag the Person field from the People table.
o Values: Drag the Sales by Employee measure.
Modify settings in the Matrix visual.
Return Rate by Employee
• Visual Type: Area
o X-axis: Drag the Person field from the People table.
o Y-axis: Drag the Return Rate by Employee measure.
Employee Profit Contribution
• Visual Type: Pie
o Legend: Drag the Person field from the People table.
o Values: Drag the Employee Profit Contribution measure.
Returned Sales by Product Category
Visual Type: Clustered Column Chart
o X-axis: Drag the Category field from the Orders table.
o Y-axis: Drag the Return Rate by Employee and Returned Sales measure.
Employee Profit Contribution, Return Rate by Employee, Sales by employee
• Visual Type: Multi-row Card
o Fields: Drag the Employee Profit Contribution, Return Rate by Employee, Sales by employee
o Format the fields, and change the format of currency to OMR
5. Using Filters and Slicers
• Slicers:
o Add slicers for fields like Category, Region, Segment, or Employee Name. These allow users to filter the dashboard by specific categories, regions, or
employees.
o Example: Drag the Category field onto a Slicer visual to let users filter the dashboard based on product categories.
• Drill-Down:
o Enable drill-down in charts with a date hierarchy (like Order Date), so users can drill from Year to Quarter, Month, and Day by clicking on the data
points in the chart.
o Power BI automatically sets up drill-down when a date hierarchy is used in a visual.
Final outcome!!!
• All the visuals should be formatted according to samples given on page #8, 9 and 10
• Publish the Project to Power BI Service
• Share the dashboard with your colleagues
• Configure the refresh times for [Link] file
• Modify the data in the [Link] file
• Ask your colleagues if they are able to see updated information on the dashboards