top of page

 

Retail Sales & Profitability Dashboard 

 

​

​

 

 

 

1. The Mission (Project Context)

In a competitive retail environment, increasing revenue is only half the battle. This project was born from a need to look past 'Top-Line' sales and uncover the 'Hidden Leaks' in the bottom line. My mission was to build a tool that allows executives to instantly identify which products, regions, and categories are truly driving growth versus those that are simply consuming resources without generating profit.

​

2. The Interactive Stack (The Visuals)

  • Screen 1: Executive View

​​​

​​

​​​​​

​​​

​​​

​​​

​​​

​​​

​​​

​​​

​​​

​​​

​

​

​

​

​

  • Screen 2: Customer View

​

​

​

​

​

​

​​

​​

​​

​​​

​

​

​

​

​

​

​

​

  • Screen 3: Profitability View 

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​​​​​

​

 

3. Technical Architecture

  • Data Modeling: Designed a Star Schema to separate Order Transactions from Dimension tables (Geography, Product, and Date), reducing calculation lag and improving filter responsiveness.

  • Dynamic DAX Logic: Developed a Metric Toggle using SWITCH and SELECTEDVALUE measures, allowing the user to switch the entire report view between Absolute Profit ($) and Profit Margin (%) with a single click.

  • Time Intelligence: Engineered custom YTD (Year-To-Date) and Prior Year comparison measures to provide context for seasonal sales spikes.

​

4. Key Business Insights ("The Discovery")

  • The 'Furniture' Paradox: Despite being a Top 3 revenue driver, the Furniture category (specifically Tables) is the company's largest profit drain, losing an average of $X per transaction.

  • Logistics Drag: The Central Region consistently shows a higher rate of 'unprofitable sales' compared to the West. Cross-referencing this with shipping data suggests that bulk-item logistics costs are eroding regional margins.

  • Technology Upsell: The Technology category maintains a 12% higher margin than Office Supplies. There is a clear opportunity to bundle high-margin tech accessories with high-volume office supply orders.

​

5. Strategic Recommendations

  • Pricing Adjustments: Implement a 5–8% price floor increase for the 'Tables' sub-category or introduce a regional 'Bulky Item' shipping surcharge to offset logistics losses.

  • Resource Allocation: Re-allocate 10% of the marketing budget from underperforming Furniture lines to the high-margin Technology sector.

  • Regional Efficiency: Conduct a logistics audit of the Central Region's 3PL (Third-Party Logistics) providers to identify why shipping costs are significantly higher than the national average.

​

6. Let’s Talk About the Data

The technical highlight of this project was creating the 'Unprofitability Threshold' measure. I wanted to create a system where any product falling below a 5% margin was automatically flagged in red. This 'Exception Reporting' allows managers to ignore the 90% of things going right and focus immediately on the 10% that need intervention.

​​

​

​

Retail Sales & Profitability Dashboard – Build Guide

​

This build guide provides complete step-by-step instructions to reproduce the Retail Sales & Profitability Dashboard in Power BI. It covers dataset loading, data modeling, DAX measures, visuals, formatting, layout, and design standards. All sizes and positions are included for reproducibility.

 

1. Dataset & Model

Dataset: Sample Superstore – Orders.

Load into Power BI Desktop

 

Create a Date table with the following DAX:

Date =

VAR MinDate = DATE(YEAR(MIN(Orders[Order Date])),1,1)

VAR MaxDate = DATE(YEAR(MAX(Orders[Order Date])),12,31)

RETURN

ADDCOLUMNS(

               CALENDAR(MinDate, MaxDate),

               “Year”, YEAR([Date]),

               “MonthNum”, MONTH ([Date]),

               “Month”, FORMAT([Date], “MMMM”),

               “YearMonth”, FORMAT([Date], “YYYY-MM”)

)

 

Sort Month by MonthNum. Mark as Date Table.

If Order Date is text, convert it to Date type first (Data view → select column → Data type: Date).

Create the relationship

  1. Go to Model view.

  2. Drag Date[Date] → Orders[Order Date].

  3. Set Cardinality: One-to-many (1:), Cross filter: Single, *Make this relationship active: On.

You now have a reusable Date table with Date, Month, MonthNum, Year, YearMonth and it’s related to Orders[Order Date] for YTD, MTD, trends, slicers, etc.

 

Create a table called Unprofitability View.

  • View Option (Data type: Text, Format: Text)

Populate this with the two options (e.g. via an Enter Data table or a disconnected dimension):

  • Unprofitable % of Sales (Sim)

  • Unprofitable Sales (Sim)

This column is used in a Slicer to drive which metric is shown.

​

Measures

Paste these into the Unprofitability View table (Modeling → New measure). They reference your existing measures

[Unprofitable % of Sales (Sim)] and [Unprofitable Sales (Sim)].

​

1. Display Measure (formatted string)

Unprofitability Measure =

SWITCH (

        SELECTEDVALUE (‘Unprofitability View’[View Option]),

        “Unprofitable % of Sales (Sim)”,

                       FORMAT([Unprofitable % of Sales (Sim)], “0.00 %”),

        “Unprofitable Sales (Sim)”,

                       FORMAT([Unprofitable Sales (Sim)], “$#,##0”)

)

2. Label (useful for cards/titles)

Unprofitability Measure Label =

SWITCH(

        SELECTEDVALUE(‘Unprofitability View’[View Option]),

        “Unprofitable % of Sales (Sim)”, FORMAT([Unprofitable % of Sales (Sim)], “0.00 %”),

        “Unprofitable Sales (Sim)”, FORMAT([Unprofitable Sales (Sim)], “$#,##0”)

)

 

3. Numeric Value (use in charts, conditional formatting, tooltips)

Unprofitability Measure Value =

SWITCH (

        SELECTEDVALUE(‘Unprofitability View’[View Option]),

        “Unprofitable % of Sales (Sim)”, [Unprofitable % of Sales (Sim)],

        “Unprofitable Sales (Sim)”, [Unprofitable Sales (Sim)]

)

 

When to use which

  • Card/KPI: Unprofitability Measure (shows the properly formatted figure).

  • Chart y-axis: Unprofitability Measure Value (keeps it numeric so axes/legends aggregate correctly).

  • Dynamic titles/data labels: Unprofitability Measure Label.

 

2. Measures

Total Profit = SUM(‘Orders’[Profit])

Total Sales = SUM(‘Orders’[Sales])

Profit Margin % = DIVIDE([Total Profit], [Total Sales])

Profit YTD = TOTALYTD([Total Profit], ‘Date’[Date])

Sales YTD = TOTALYTD([Total Sales], ‘Date’[Date])

Sales Prev Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(‘Date’[Date]))

Sales ∆ vs PY = [Total Sales] – [Sales Prev Year]

Sales ∆% vs PY = DIVIDE([Sales ∆ vs PY], [Sales Prev Year])

Distinct Customers = DISTINCTCOUNT(‘Orders’[Customer ID])

Sales per Customer = DIVIDE([Total Sales], [Distinct Customers])

Distinct Orders = DISTINCTCOUNT(‘Orders’[Order ID])

Avg Order Value (AOV) = DIVIDE([Total Sales], [Distinct Orders])

Orders per Customer = DIVIDE([Distinct Orders], [Distinct Customers])

Unprofitable Sales = CALCULATE([Total Sales], ‘Orders’[Profit] < 0)

 

New Columns

Sim Unprofitable? =

VAR last2 = VALUE(RIGHT(‘Orders’[Order ID],2))

RETURN MOD (last2, 10) = 0

 

Profit Adjusted =

IF(‘Orders’[Sim Unprofitable?],

  • ABS (‘Orders’[Profit]),

‘Orders’[Profit]

)

 

New Measures

Low Margin Sales (<10%) (Sim) = CALCULATE([Total Sales], DIVIDE(‘Orders’[Profit Adjusted], ‘Orders’[Sales]) < 0.10)

Unprofitable Sales (Sim) = CALCULATE([Total Sales], ‘Orders’[Profit Adjusted] < 0)

Unprofitable % of Sales (Sim) = DIVIDE([Unprofitable Sales (Sim), [Total Sales])

Low Margin Sales (<10%) (Sim) = CALCULATE([Total Sales], DIVIDE(‘Orders’[Profit Adjusted], ‘Orders’[Sales]) < 0.10)

Low Margin % of Sales (Sim) = DIVIDE([Low Margin Sales (<10%) (Sim)], [Total Sales])

 

Date Hierarchy = Date, Month, and Year

Category Hierarchy = Category, Sub-Category, and Product Name

Country Hierarchy = Country, Region, State, and City

 

3. Executive View

Canvas: Custom: H 750 px, W 1500 px

 

  • Insert Text Box → ‘Retail Sales & Profitability Dashboard’

       Font: Segoe UI, 20 pt, bold, centered

       Size: H 50, W 1024, Pos: H 20, V 0

 

  • Insert Text Box → ‘Page 1: High-Level Performance Overview. High-level performance overview of retail sales and profitability, using a simulated dataset to replicate common retail challenges.’

       Font: 10 pt, centered

       Size: H 30, W 1024, Pos: H 20, V 50

 

  • Card (new) → Total Sales, Total Profit, Profit Margin %, and Avg Order Value (AOV).

       Size: H 128, W 1024, Pos: H 20, V 80

 

  • Slicer: Category → Tile → Size: H 96, W 464, Pos: H 20, V 208

  • Slicer: Year → Dropdown → Size: H 96, W 160, Pos: H 480, V 208

  • Slicer: YearMonth → Dropdown → Size: H 96, W 144, Pos: H 640, V 208

  • Button: Clear all slicers → Size: H 48, W 96, Pos: H 784, V 208

 

1. Line chart → X: Date Hierarchy, Y: Sales YTD + Profit YTD, Tooltips: Total Sales + Profit YTD                                       

Title: YTD Sales & Profit                                                                                                                                                  Size: H 208, W 432, Pos: H 1068, V 0                                                                                                                              Colors: Sales #2ECC71 (green), Profit #8E44AD (purple)

​​

2. Line chart → X: Date Hierarchy, Y: Total Sales + Sales Prev Year, Tooltips: Sales ∆ vs PY, Sales ∆ % vs PY                          Title: Sales vs Previous Year                                                                                                                                            Size: H 240, W 608, Pos: H 892, V 208                                                                                                                               

Colors: Total Sales #27AE60 (green), Sales Prev Year #2980B9 (blue)       

 

3. Clustered column chart → X: Category Hierarchy, Y: Total Sales, Tooltips: Total Profit, Profit Margin %                              Title: Category Sales Breakdown                                                                                                                                    Size: H 240, W 672, Pos: H 828, V 464                                                                                                                       Conditional colors: Technology = #2ECC71 (green), Office Supplies = #3498DB (blue), Furniture = #E67E22 (orange)

 

4. Clustered bar chart → Y: Country Hierarchy, X: Total Sales, Tooltips: Total Profit, Profit Margin %           

Title: Sales & Profitability                                                                                                                                                      Size: H 272, W 656, Pos: H 20, V 448                                                                                                                          

Data Labels: On → Gradient color by Country Hierarchy Region → #FA0000 (red) → #B800FB (purple) → #00F93E (green)

​

5. Line chart → X: Date Hierarchy, Y: Total Sales                                                                                                         

Title: Sales Trend Over Time                                                                                                                                                  Color: #16A085 (turquoise)                                                                                                                                              Size: H 144, W 656, Pos: H 20, V 305

 

  • Insert Text Box → ‘Sales show steady overall growth, though performance varies by region and category. Technology drives profit; Office Supplies require margin improvement.’                                                                                   Font: 10 pt, centered                                                                                                                                               Size: H 144, W 656, Pos: H 20, V 305

 

4. Customer View

Canvas: Custom: H 750 px, W 1500 px

 

  • Insert Text Box → ‘Retail Sales & Profitability Dashboard’

       Font: Segoe UI, 20 pt, bold, centered

       Size: H 50, W 768, Pos: H 20, V 0

 

  • Insert Text Box → ‘Page 2: Customer Profitability & Behavior. Customer-level analysis highlighting profitability distribution and sales per customer.’

       Font: 10 pt, centered

       Size: H 30, W 768, Pos: H 20, V 50

 

  • Card (new): Distinct Customers, Orders per Customer, and Sales per Customer.                                                      Size: H 128, W 768, Pos: H 20, V 80

 

 

  • Slicer: Category → Title → Size: H 96, W 464, Pos: H 20, V 208                                                                              

  • Slicer: YearMonth → Dropdown → Size: H 96, W 160, Pos: H 479, V 208

  • Button: Clear all slicers → Size: H 48, W 112, Pos: H 640, V 206

 

1. Table →  Columns: Customer Name, Total Sales, Total Profit, Profit Margin %, Orders per Customer, and Sales per Customer                                                                                                                                                                      Title: Customers by Sales                                                                                                                                             

Size: H 605, W 710, Pos: H 785, V 112

 

2. Scatter → Values: Customer Name, X: Sales per Customer, Y: Profit Margin %                                                                    Title: Customer Profitabililty                                                                                                                                                  Gradient color by Profit Margin % → #FA0000 (red) → #B800FB (purple) → #00F93E (green)            

Size: H 416, W 750, Pos: H 20, V 304

 

  • Insert Text Box → ‘Margins are stable for most customers, while a select few generate outsized revenue. Targeted engagement with these key accounts can boost profitability.’                                                                                  Font: 10 pt, centered                                                                                                                                                      Size: H 30, W 976, Pos: H 20, V 720

 

5. Profitability View

Canvas: Custom: H 750 px, W 1500 px

 

  • Insert Text Box → ‘Retail Sales & Profitability Dashboard’

       Font: Segoe UI, 20 pt, bold, centered

       Size: H 50, W 1024, Pos: H 20, V 0

 

  • Insert Text Box → ‘Page 3: Product & Regional Profitability. Product-level profitability risk analysis across categories, sub-categories, and geographies.’

       Font: 10 pt, centered

       Size: H 30, W 1024, Pos: H 20, V 50

 

  • Card (new): Low Margin % of Sales (Sim), Low Margin Sales (<10%) (Sim), Unprofitable Sales (Sim), and Unprofitable % of Sales (Sim)                                                                                                                                                          Size: H 128, W 1024, Pos: H 20, V 80

 

  • Slicer: Category → Tile → Size: H 96, W 464, Pos: H 20, V 208

 

  • Slicer: YearMonth → Dropdown → Size: H 96, W 160, Pos: H 479, V 208

 

  • Button: Clear all slicers → Size: H 48, W 112, Pos: H 640, V 208

 

  • Slicer: View Option → Vertical list → Size: H 96, W 252, Pos: H 816, V 304

 

 

1. Scatter → Value: Category Hierarchy, X: Total Sales, Y: Profit Margin %, Size: Total Sales, Tooltips: Total Sales, Profit Margin %, Unprofitable Sales (Sim), and Unprofitable % of Sales (Sim)             

Title: Profitability by Product

Gradient color by Profit Margin % → #FA0000 (red) → #B800FB (purple) → #00F93E (green)

Size: H 416, W 800, Pos: H 20, V 304

 

2. Map → Location: State, Bubble size: Unprofitability Measure Value, Tooltips: Total Sales + Unprofitability Measure Label

Title: Sales & Profitability by State

Gradient color by Unprofitable % of Sales (Sim) → #00F93E (green) → #B800FB (purple) → #FA0000 (red)

Size: H 400, W 432, Pos: H 1068, V 0

 

3. Line and clustered column chart → X: Category Hierarchy, Column Y: Unprofitability Measure Value, Line Y: Unprofitable % of Sales (Sim), Tooltips: Unprofitable Sales (Sim), Unprofitability Measure Label

Title: Profitability by Category Hierarchy

Size: H 304, W 680, Pos: H 820, V 416

Conditional colors: Technology = #2ECC71 (green), Office Supplies = #3498DB (blue), Furniture = #E67E22 (orange)

 

  • Insert Text Box → ‘High unprofitable sales in key sub-categories such as Tables and Bookcases highlight opportunities to improve pricing and reduce losses.’

       Font: 10 pt, centered

       Size: H 30, W 930, Pos: H 20, V 720​​

image.png
image.png

​

bottom of page