power-bi-retail-analytics-dashboard

Power BI Retail Analytics Dashboard Skill

Skill by ara.so — Data Skills collection.

This skill enables AI coding agents to work with SalesPulse 360, a comprehensive Power BI retail analytics dashboard designed for multi-dimensional sales, profit, and regional analysis. The project transforms raw sales data into interactive visualizations with predictive forecasting, exception alerts, and multi-granularity filtering.

What This Project Does

SalesPulse 360 is a production-ready Power BI dashboard that:

Visualizes retail performance across regions, product categories, customer segments, and time periods
Provides predictive analytics using built-in Power BI forecasting for 12-month projections
Generates automated alerts when KPIs breach configurable thresholds (profit margin, return rate)
Supports multi-language localization with currency and date format adaptation
Enables drill-down analysis from global summaries to individual transaction details
Auto-refreshes data on scheduled intervals via Power BI Service

The dashboard is built on the Global Superstore dataset (2011-2015) but can be adapted to any retail/sales data source.

Installation & Setup

Prerequisites

Power BI Desktop (version 2.120 or higher) - Download here
Power BI Service account (for publishing and scheduled refresh)
Modern web browser for viewing published dashboards

Step 1: Clone the Repository

git clone https://github.com/MahbubNibir/power-bi-retail-analytics-viz.git
cd power-bi-retail-analytics-viz

Step 2: Extract Dataset

The Global Superstore dataset is included as a compressed file:

# Extract the dataset (location varies by OS)
# Windows
tar -xf data/GlobalSuperstore.zip -C data/

# macOS/Linux
unzip data/GlobalSuperstore.zip -d data/

Step 3: Open the Power BI File

# Open the main dashboard file
start SalesPulse360.pbix  # Windows
open SalesPulse360.pbix   # macOS

On first load, Power BI Desktop will:

Connect to the CSV data source
Run Power Query transformations (data cleaning, normalization)
Load the data model (typically 2-3 minutes)

Step 4: Update Data Source Path (if needed)

If you've moved the dataset to a custom location:

Go to Home → Transform Data → Data Source Settings
Select the CSV source
Click Change Source
Update the file path to your CSV location
Click Close & Apply

Key Components & Architecture

Data Model Structure

The dashboard uses a star schema:

Fact Table: Orders
├── OrderID (unique identifier)
├── OrderDate, ShipDate
├── Sales, Profit, Quantity, Discount
└── Foreign keys to dimensions

Dimension Tables:
├── DimCustomer (CustomerID, Segment, CustomerName)
├── DimProduct (ProductID, Category, SubCategory, ProductName)
├── DimLocation (Region, Country, State, City, PostalCode)
└── DimDate (Date, Year, Quarter, Month, Week, DayOfWeek)

DAX Measures Library

Key calculated measures used throughout the dashboard:

-- Total Sales (baseline measure)
Total Sales = SUM(Orders[Sales])

-- Profit Margin (percentage)
Profit Margin % = 
DIVIDE(
    SUM(Orders[Profit]),
    SUM(Orders[Sales]),
    0
)

-- Moving Annual Total (MAT)
Sales MAT = 
CALCULATE(
    [Total Sales],
    DATESINPERIOD(
        DimDate[Date],
        LASTDATE(DimDate[Date]),
        -12,
        MONTH
    )
)

-- Year-over-Year Growth
YoY Growth % = 
VAR CurrentPeriodSales = [Total Sales]
VAR PreviousYearSales = 
    CALCULATE(
        [Total Sales],
        SAMEPERIODLASTYEAR(DimDate[Date])
    )
RETURN
DIVIDE(
    CurrentPeriodSales - PreviousYearSales,
    PreviousYearSales,
    0
)

-- Profit Alert Flag (for exception reporting)
Profit Alert = 
IF(
    [Profit Margin %] < 0.15,  -- 15% threshold
    "⚠ Low Margin",
    "✓ Healthy"
)

-- Customer Retention Index
Retention Index = 
VAR RepeatCustomers = 
    CALCULATE(
        DISTINCTCOUNT(Orders[CustomerID]),
        Orders[OrderCount] > 1
    )
VAR TotalCustomers = DISTINCTCOUNT(Orders[CustomerID])
RETURN
DIVIDE(RepeatCustomers, TotalCustomers, 0)

Power Query Transformations

The ETL pipeline in Power Query performs:

// Remove duplicates and null values
let
    Source = Csv.Document(File.Contents("data/GlobalSuperstore.csv")),
    Headers = Table.PromoteHeaders(Source),
    CleanedData = Table.RemoveRowsWithErrors(Headers),
    
    // Type conversions
    TypedData = Table.TransformColumnTypes(CleanedData, {
        {"Order Date", type date},
        {"Ship Date", type date},
        {"Sales", Currency.Type},
        {"Profit", Currency.Type},
        {"Quantity", Int64.Type},
        {"Discount", Percentage.Type}
    }),
    
    // Add calculated columns
    WithProfitMargin = Table.AddColumn(
        TypedData,
        "Profit Margin",
        each [Profit] / [Sales],
        Percentage.Type
    ),
    
    // Remove irrelevant columns
    FinalTable = Table.SelectColumns(WithProfitMargin, {
        "Order ID", "Order Date", "Ship Date",
        "Customer ID", "Customer Name", "Segment",
        "Product ID", "Category", "Sub-Category",
        "Region", "Country", "State", "City",
        "Sales", "Profit", "Quantity", "Discount", "Profit Margin"
    })
in
    FinalTable

Configuration & Customization

Configuring Alert Thresholds

The dashboard includes a Settings table for configurable parameters:

Go to Data view in Power BI Desktop
Locate the ConfigSettings table
Modify threshold values:

Parameter	Default	Description
`ProfitMarginThreshold`	0.15	Minimum acceptable profit margin (15%)
`ReturnRateWarning`	0.08	Return rate trigger for alerts (8%)
`RollingAvgDays`	90	Period for moving averages
`ForecastMonths`	12	Months to project in forecasts

These values are referenced in DAX measures:

Dynamic Profit Alert = 
VAR Threshold = SELECTEDVALUE(ConfigSettings[ProfitMarginThreshold])
RETURN
IF([Profit Margin %] < Threshold, "⚠", "✓")

Adding Multi-Language Support

To add a new language translation:

Create a new table Translations with columns:
- Key (English label)
- Language (ISO code: en, es, fr, de, zh)
- Translation (localized text)
Use this DAX pattern for dynamic labels:

Dynamic Label = 
VAR SelectedLanguage = SELECTEDVALUE(Settings[Language], "en")
VAR LabelKey = "Total Sales"
RETURN
LOOKUPVALUE(
    Translations[Translation],
    Translations[Key], LabelKey,
    Translations[Language], SelectedLanguage,
    LabelKey  -- fallback to English
)

Setting Up Scheduled Refresh

After publishing to Power BI Service:

Navigate to your workspace → Settings → Datasets
Select SalesPulse360 dataset
Under Scheduled refresh:
- Enable Keep your data up to date
- Set frequency: Daily at 02:00 UTC
- Add data source credentials if using database connections
For real-time data, configure Streaming datasets instead:
- Create a REST API dataset
- Push data using Power Automate or custom scripts

Common Usage Patterns

Pattern 1: Regional Drilldown Analysis

To analyze underperforming regions:

-- Create a measure for regional ranking
Region Profit Rank = 
RANKX(
    ALL(DimLocation[Region]),
    [Total Profit],
    ,
    DESC,
    DENSE
)

-- Filter to bottom 25% regions
Bottom Regions = 
CALCULATE(
    [Total Profit],
    FILTER(
        ALL(DimLocation[Region]),
        [Region Profit Rank] > COUNTROWS(ALL(DimLocation[Region])) * 0.75
    )
)

Use in a table visual with drill-through to city-level details.

Pattern 2: Predictive Forecasting

Enable forecasting on a line chart:

Add a Line Chart visual with:
- X-axis: DimDate[Date]
- Y-axis: [Total Sales]
In the Analytics pane:
- Add Forecast
- Set Forecast length: 12 months
- Set Confidence interval: 95%
- Seasonality: Auto-detect

The forecast uses exponential smoothing:

-- Manual forecast calculation (alternative to built-in)
Sales Forecast = 
VAR HistoricalAvg = CALCULATE([Total Sales], DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]), -12, MONTH))
VAR SeasonalityFactor = 
    DIVIDE(
        CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date])),
        HistoricalAvg,
        1
    )
RETURN
HistoricalAvg * SeasonalityFactor

Pattern 3: Exception Highlighting with Conditional Formatting

To highlight cells where profit margin < 15%:

Select a Table visual
Right-click the Profit Margin % column → Conditional formatting → Background color
Use this DAX rule:

Profit Margin Color = 
SWITCH(
    TRUE(),
    [Profit Margin %] < 0.10, "#E74C3C",  -- Red (< 10%)
    [Profit Margin %] < 0.15, "#F39C12",  -- Orange (10-15%)
    "#27AE60"                              -- Green (>= 15%)
)

Pattern 4: Dynamic Title with Current Filter Context

Create a dynamic dashboard title that reflects active filters:

Dashboard Title = 
VAR SelectedRegion = IF(ISFILTERED(DimLocation[Region]), SELECTEDVALUE(DimLocation[Region]), "All Regions")
VAR SelectedCategory = IF(ISFILTERED(DimProduct[Category]), SELECTEDVALUE(DimProduct[Category]), "All Categories")
VAR SelectedYear = IF(ISFILTERED(DimDate[Year]), SELECTEDVALUE(DimDate[Year]), "All Years")
RETURN
"SalesPulse 360 | " & SelectedRegion & " | " & SelectedCategory & " | " & SelectedYear

Working with Custom Data Sources

Connecting to SQL Server

Replace the CSV source with a live database:

Get Data → SQL Server
Server: your-server.database.windows.net
Database: SalesDB
Connection mode: Import (for small datasets) or DirectQuery (for large datasets)

Query example:

SELECT 
    o.OrderID,
    o.OrderDate,
    o.ShipDate,
    c.CustomerName,
    c.Segment,
    p.ProductName,
    p.Category,
    p.SubCategory,
    l.Region,
    l.Country,
    l.State,
    o.Sales,
    o.Profit,
    o.Quantity,
    o.Discount
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
JOIN Locations l ON o.LocationID = l.LocationID
WHERE o.OrderDate >= '2020-01-01'

Using Environment Variables for Credentials

For database connections in Power Query:

let
    Server = Environment.GetEnvironmentVariable("SQL_SERVER"),
    Database = Environment.GetEnvironmentVariable("SQL_DATABASE"),
    Source = Sql.Database(Server, Database)
in
    Source

Set environment variables before opening Power BI Desktop:

# Windows (PowerShell)
$env:SQL_SERVER="your-server.database.windows.net"
$env:SQL_DATABASE="SalesDB"

# macOS/Linux
export SQL_SERVER="your-server.database.windows.net"
export SQL_DATABASE="SalesDB"

Troubleshooting

Issue: Data Refresh Fails in Power BI Service

Symptom: Scheduled refresh shows "Failed" status

Solutions:

Check data source credentials:
- Go to Dataset settings → Data source credentials
- Re-enter credentials with "Skip test connection" enabled
Verify gateway connection (for on-premises data):
- Install Power BI Gateway on the data source machine
- Configure gateway in Power BI Service
- Update dataset to use the gateway

Reduce data volume:

// Add date filter in Power Query
let
    Source = ...,
    FilteredData = Table.SelectRows(Source, each [OrderDate] >= #date(2022, 1, 1))
in
    FilteredData

Issue: Dashboard Performance is Slow

Symptom: Visuals take 10+ seconds to render

Solutions:

Optimize DAX measures - avoid row-by-row iteration:

-- SLOW (row iteration)
Total Profit Slow = 
SUMX(
    Orders,
    Orders[Sales] * Orders[ProfitMargin]
)

-- FAST (column aggregation)
Total Profit Fast = SUM(Orders[Profit])

Reduce visual count - limit to 15-20 visuals per page

Use aggregations:

-- Pre-aggregate at monthly level
Monthly Sales = 
SUMMARIZE(
    Orders,
    DimDate[Year],
    DimDate[Month],
    "Sales", SUM(Orders[Sales])
)

Switch to DirectQuery for datasets > 1GB

Issue: Forecast Shows Flat Line

Symptom: Built-in forecast generates a horizontal projection

Cause: Insufficient historical data or no seasonality detected

Solutions:

Ensure at least 2 full seasonal cycles (24 months for monthly data)
Manually set seasonality:
- Select forecast → Seasonality: 12 (for monthly patterns)
Use custom DAX forecast:

Manual Forecast = 
VAR LastKnownDate = MAX(DimDate[Date])
VAR ForecastDate = SELECTEDVALUE(DimDate[Date])
VAR IsHistorical = ForecastDate <= LastKnownDate
RETURN
IF(
    IsHistorical,
    [Total Sales],  -- Historical actual
    [Sales Forecast]  -- Custom projection measure
)

Issue: Filters Not Working Across Pages

Symptom: Slicer selections don't persist when navigating pages

Solution: Configure report-level filters:

Select slicer → Format → Edit interactions
Set interaction to Filter (not None)
For cross-page filtering:
- Right-click slicer → Sync slicers
- Enable Sync for target pages

Issue: Row-Level Security Not Applying

Symptom: Users see all regions despite RLS configuration

DAX RLS Configuration:

-- In Modeling → Manage Roles → Create role "Regional Manager"
-- Filter on DimLocation table:
[Region] = USERPRINCIPALNAME()

-- Or using a security mapping table:
[Region] IN 
CALCULATETABLE(
    VALUES(SecurityMapping[Region]),
    FILTER(
        SecurityMapping,
        SecurityMapping[UserEmail] = USERPRINCIPALNAME()
    )
)

Testing:

Modeling → View as Roles
Select Regional Manager role
Enter test email: user@domain.com

Real-World Code Examples

Example 1: Customer Cohort Analysis

Track customer behavior by cohort (month of first purchase):

-- Calculate first purchase month for each customer
First Purchase Month = 
CALCULATE(
    MIN(Orders[OrderDate]),
    ALLEXCEPT(Orders, Orders[CustomerID])
)

-- Cohort label
Cohort = FORMAT([First Purchase Month], "YYYY-MM")

-- Retention by cohort (months since first purchase)
Months Since First Purchase = 
DATEDIFF(
    [First Purchase Month],
    MAX(DimDate[Date]),
    MONTH
)

-- Cohort retention rate
Cohort Retention % = 
VAR CohortSize = 
    CALCULATE(
        DISTINCTCOUNT(Orders[CustomerID]),
        FILTER(ALL(DimDate), DimDate[Date] = [First Purchase Month])
    )
VAR ActiveInPeriod = DISTINCTCOUNT(Orders[CustomerID])
RETURN
DIVIDE(ActiveInPeriod, CohortSize, 0)

Create a matrix visual:

Rows: [Cohort]
Columns: [Months Since First Purchase]
Values: [Cohort Retention %]

Example 2: ABC Product Classification

Classify products by contribution to profit (Pareto analysis):

-- Calculate cumulative profit percentage
Product Profit % = 
DIVIDE(
    SUM(Orders[Profit]),
    CALCULATE(SUM(Orders[Profit]), ALL(DimProduct)),
    0
)

Cumulative Profit % = 
VAR CurrentProduct = SELECTEDVALUE(DimProduct[ProductID])
VAR RankPosition = 
    RANKX(
        ALL(DimProduct),
        [Product Profit %],
        ,
        DESC,
        DENSE
    )
RETURN
CALCULATE(
    SUM([Product Profit %]),
    FILTER(
        ALL(DimProduct),
        RANKX(ALL(DimProduct), [Product Profit %], , DESC) <= RankPosition
    )
)

-- ABC Classification
ABC Class = 
SWITCH(
    TRUE(),
    [Cumulative Profit %] <= 0.70, "A - Top 70%",
    [Cumulative Profit %] <= 0.90, "B - Next 20%",
    "C - Bottom 10%"
)

Example 3: Shipping Delay Alert System

Flag orders with shipping delays and calculate impact:

-- Standard shipping time by category
Expected Ship Days = 
SWITCH(
    SELECTEDVALUE(DimProduct[Category]),
    "Technology", 3,
    "Furniture", 5,
    "Office Supplies", 2,
    3  -- default
)

-- Actual shipping time
Actual Ship Days = 
DATEDIFF(Orders[OrderDate], Orders[ShipDate], DAY)

-- Delay flag
Shipping Delay = 
IF(
    [Actual Ship Days] > [Expected Ship Days],
    "⚠ Delayed",
    "✓ On Time"
)

-- Lost profit from delays (assume 2% customer churn per delayed order)
Delay Impact $ = 
CALCULATE(
    SUM(Orders[Profit]) * 0.02,
    Orders[Actual Ship Days] > [Expected Ship Days]
)

Advanced Techniques

Creating a What-If Parameter for Scenario Analysis

Add a discount scenario slider:

Modeling → New Parameter
Name: Discount Scenario
Min: 0%, Max: 50%, Increment: 5%
Default: 10%

Use in calculations:

Projected Sales = 
VAR CurrentSales = [Total Sales]
VAR DiscountImpact = [Discount Scenario Value] * 0.5  -- 50% sensitivity
RETURN
CurrentSales * (1 - DiscountImpact)

Projected Profit = 
VAR CurrentProfit = [Total Profit]
VAR DiscountImpact = [Discount Scenario Value] * 0.8  -- 80% sensitivity
RETURN
CurrentProfit * (1 - DiscountImpact)

Implementing Drill-Through with Context Passing

Create a detail page for product deep-dive:

Create a new page: Product Details
Add Drill-through field: DimProduct[ProductName]
Add visuals showing:
- Sales trend by month
- Top customers for this product
- Profitability breakdown by region

Right-click any product in the main dashboard → Drill through → Product Details

Using Python for Advanced Clustering

Segment customers using K-means (requires Python in Power BI):

# Python script visual
import pandas as pd
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler

# Input dataset is automatically available as 'dataset'
df = dataset[['CustomerID', 'TotalSpend', 'OrderFrequency', 'AvgOrderValue']]

# Standardize features
scaler = StandardScaler()
scaled_features = scaler.fit_transform(df[['TotalSpend', 'OrderFrequency', 'AvgOrderValue']])

# K-means clustering
kmeans = KMeans(n_clusters=4, random_state=42)
df['Cluster'] = kmeans.fit_predict(scaled_features)

# Cluster labels
cluster_labels = {
    0: 'High-Value Loyalists',
    1: 'Occasional Big Spenders',
    2: 'Frequent Low-Value',
    3: 'At-Risk'
}
df['Segment'] = df['Cluster'].map(cluster_labels)

# Output must be assigned to 'dataset' for Power BI
dataset = df

Enable Python scripting:

File → Options → Python scripting
Set Python home directory (Anaconda recommended)

Best Practices

Use variables in DAX to improve readability and performance:

Profit Margin % = 
VAR TotalProfit = SUM(Orders[Profit])
VAR TotalSales = SUM(Orders[Sales])
RETURN DIVIDE(TotalProfit, TotalSales, 0)

Avoid circular dependencies - never reference a calculated column in a measure that feeds back to that column
Document measures with descriptions:
- Right-click measure → Edit description
- Add: "Calculates year-over-year growth using SAMEPERIODLASTYEAR"
Use measure groups for organization:
- Create display folders: [Sales Metrics], [Profit Metrics], [Forecasts]
Test with realistic data volumes - performance degrades non-linearly

Version control .pbix files using Git LFS:

git lfs track "*.pbix"
git add .gitattributes
git commit -m "Track Power BI files with LFS"

This skill enables comprehensive retail analytics dashboard development, from basic setup to advanced predictive modeling and custom data integrations.

About this skill

What This Project Does

Installation & Setup

Prerequisites

Step 1: Clone the Repository

Step 2: Extract Dataset

Step 3: Open the Power BI File

Step 4: Update Data Source Path (if needed)

Key Components & Architecture

Data Model Structure

DAX Measures Library

Power Query Transformations

Configuration & Customization

Configuring Alert Thresholds

Adding Multi-Language Support

Setting Up Scheduled Refresh

Common Usage Patterns

Pattern 1: Regional Drilldown Analysis

Pattern 2: Predictive Forecasting

Pattern 3: Exception Highlighting with Conditional Formatting

Pattern 4: Dynamic Title with Current Filter Context

Working with Custom Data Sources

Connecting to SQL Server

Using Environment Variables for Credentials

Troubleshooting

Issue: Data Refresh Fails in Power BI Service

Issue: Dashboard Performance is Slow

Issue: Forecast Shows Flat Line

Issue: Filters Not Working Across Pages

Issue: Row-Level Security Not Applying

Real-World Code Examples

Example 1: Customer Cohort Analysis

Example 2: ABC Product Classification

Example 3: Shipping Delay Alert System

Advanced Techniques

Creating a What-If Parameter for Scenario Analysis

Implementing Drill-Through with Context Passing

Using Python for Advanced Clustering

Best Practices