# Power Bi Retail Analytics Dashboard

> Power BI retail sales dashboard for multi-dimensional profit, regional, and sales analysis with predictive insights

- Skill: `aradotso/power-bi-retail-analytics-dashboard` (Agent Skill)
- Install (CLI): `npx skillmds@latest add aradotso/power-bi-retail-analytics-dashboard`
- Raw SKILL.md: https://api.skillmd.com/api/skills/aradotso/power-bi-retail-analytics-dashboard/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: aradotso (https://skillmd.com/u/aradotso)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/aradotso/power-bi-retail-analytics-dashboard

---


# Power BI Retail Analytics Dashboard Skill

> Skill by [ara.so](https://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](https://powerbi.microsoft.com/desktop/)
- **Power BI Service** account (for publishing and scheduled refresh)
- Modern web browser for viewing published dashboards

### Step 1: Clone the Repository

```bash
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:

```bash
# 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

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

On first load, Power BI Desktop will:
1. Connect to the CSV data source
2. Run Power Query transformations (data cleaning, normalization)
3. 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:

1. Go to **Home** → **Transform Data** → **Data Source Settings**
2. Select the CSV source
3. Click **Change Source**
4. Update the file path to your CSV location
5. 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:

```dax
-- 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:

```m
// 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:

1. Go to **Data** view in Power BI Desktop
2. Locate the **ConfigSettings** table
3. 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:

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

### Adding Multi-Language Support

To add a new language translation:

1. Create a new table **Translations** with columns:
   - `Key` (English label)
   - `Language` (ISO code: en, es, fr, de, zh)
   - `Translation` (localized text)

2. Use this DAX pattern for dynamic labels:

```dax
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:

1. Navigate to your workspace → **Settings** → **Datasets**
2. Select **SalesPulse360** dataset
3. 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

4. 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:

```dax
-- 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:

1. Add a **Line Chart** visual with:
   - X-axis: `DimDate[Date]`
   - Y-axis: `[Total Sales]`

2. In the **Analytics** pane:
   - Add **Forecast**
   - Set **Forecast length**: 12 months
   - Set **Confidence interval**: 95%
   - Seasonality: Auto-detect

The forecast uses exponential smoothing:

```dax
-- 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%:

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

```dax
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:

```dax
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:

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

Query example:

```sql
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:

```m
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:

```bash
# 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**:

1. **Check data source credentials**:
   - Go to **Dataset settings** → **Data source credentials**
   - Re-enter credentials with "Skip test connection" enabled

2. **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

3. **Reduce data volume**:
   ```m
   // 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**:

1. **Optimize DAX measures** - avoid row-by-row iteration:

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

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

2. **Reduce visual count** - limit to 15-20 visuals per page

3. **Use aggregations**:
   ```dax
   -- Pre-aggregate at monthly level
   Monthly Sales = 
   SUMMARIZE(
       Orders,
       DimDate[Year],
       DimDate[Month],
       "Sales", SUM(Orders[Sales])
   )
   ```

4. **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**:

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

3. Use custom DAX forecast:

```dax
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:

1. Select slicer → **Format** → **Edit interactions**
2. Set interaction to **Filter** (not **None**)
3. 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**:

```dax
-- 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**:
1. **Modeling** → **View as Roles**
2. Select **Regional Manager** role
3. Enter test email: `user@domain.com`

## Real-World Code Examples

### Example 1: Customer Cohort Analysis

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

```dax
-- 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):

```dax
-- 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:

```dax
-- 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:

1. **Modeling** → **New Parameter**
2. Name: `Discount Scenario`
3. Min: 0%, Max: 50%, Increment: 5%
4. Default: 10%

Use in calculations:

```dax
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:

1. Create a new page: **Product Details**
2. Add **Drill-through** field: `DimProduct[ProductName]`
3. 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
# 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:
1. **File** → **Options** → **Python scripting**
2. Set Python home directory (Anaconda recommended)

## Best Practices

1. **Use variables in DAX** to improve readability and performance:
   ```dax
   Profit Margin % = 
   VAR TotalProfit = SUM(Orders[Profit])
   VAR TotalSales = SUM(Orders[Sales])
   RETURN DIVIDE(TotalProfit, TotalSales, 0)
   ```

2. **Avoid circular dependencies** - never reference a calculated column in a measure that feeds back to that column

3. **Document measures** with descriptions:
   - Right-click measure → **Edit description**
   - Add: `"Calculates year-over-year growth using SAMEPERIODLASTYEAR"`

4. **Use measure groups** for organization:
   - Create display folders: `[Sales Metrics]`, `[Profit Metrics]`, `[Forecasts]`

5. **Test with realistic data volumes** - performance degrades non-linearly

6. **Version control .pbix files** using Git LFS:
   ```bash
   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.