power-bi-salespulse-360-dashboard

Power BI SalesPulse 360 Dashboard Skill

Skill by ara.so — Data Skills collection.

Overview

SalesPulse 360 is a comprehensive Power BI dashboard for retail analytics, designed to transform the Global Superstore dataset into actionable business intelligence. This dashboard provides:

Multi-dimensional sales analysis across regions, categories, and time periods
Profit architecture mapping with heatmaps and margin analysis
Predictive forecasting for 12-month sales and profit trends
Real-time alerts for profit margin drops and anomaly detection
Multilingual support with automatic localization
Responsive design for cross-device viewing

The project is built entirely in Power BI Desktop using DAX (Data Analysis Expressions) and Power Query M for data transformation.

Installation & Setup

Prerequisites

Power BI Desktop version 2.120 or higher (download here)
Global Superstore dataset (included in repository as CSV)
Minimum 8GB RAM for smooth performance

Initial Setup

Clone the repository:

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

Extract the dataset:

# Extract the Global Superstore CSV from the data archive
unzip data/GlobalSuperstore.zip -d data/

Open the dashboard:
- Launch Power BI Desktop
- Open SalesPulse360.pbix
- Wait for initial data transformation (2-3 minutes)
Verify data connection:
- Navigate to Home > Transform Data
- Check that all queries are loading successfully
- Close the Power Query Editor

Project Structure

power-bi-retail-analytics-viz/
├── SalesPulse360.pbix          # Main Power BI dashboard file
├── data/
│   ├── GlobalSuperstore.csv    # Source dataset (2011-2015)
│   └── ConfigThresholds.csv    # Alert threshold configuration
├── scripts/
│   ├── DataCleaningSteps.txt   # Power Query M transformation log
│   └── DAXMeasures.txt         # All DAX formulas used
├── preview.svg                 # Dashboard preview image
└── README.md

Core Components

1. Data Model Architecture

The dashboard uses a star schema with the following structure:

Fact Table:

Sales (Orders, Sales Amount, Profit, Discount, Quantity, Shipping Cost)

Dimension Tables:

Calendar (Date, Year, Quarter, Month, Week)
Geography (Region, Country, State, City, Postal Code)
Product (Category, Sub-Category, Product Name)
Customer (Customer ID, Segment)

Relationships:

Sales[Order Date] → Calendar[Date] (Many-to-One)
Sales[Postal Code] → Geography[Postal Code] (Many-to-One)
Sales[Product ID] → Product[Product ID] (Many-to-One)
Sales[Customer ID] → Customer[Customer ID] (Many-to-One)

2. Key DAX Measures

Total Sales

Total Sales = SUM(Sales[Sales Amount])

Total Profit

Total Profit = SUM(Sales[Profit])

Profit Margin %

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

Sales vs Previous Period

Sales vs Previous Period = 
VAR CurrentSales = [Total Sales]
VAR PreviousSales = 
    CALCULATE(
        [Total Sales],
        DATEADD(Calendar[Date], -1, YEAR)
    )
RETURN
    DIVIDE(
        CurrentSales - PreviousSales,
        PreviousSales,
        0
    ) * 100

Moving Annual Total (MAT)

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

Profit Alert Flag

Profit Alert = 
VAR ProfitMarginThreshold = 15
VAR CurrentMargin = [Profit Margin %]
RETURN
    IF(
        CurrentMargin < ProfitMarginThreshold,
        "🔴 Alert",
        IF(
            CurrentMargin < ProfitMarginThreshold + 5,
            "🟡 Caution",
            "🟢 Healthy"
        )
    )

12-Month Sales Forecast

Sales Forecast = 
VAR HistoricalData = 
    CALCULATETABLE(
        ADDCOLUMNS(
            Calendar,
            "Sales", [Total Sales]
        ),
        Calendar[Date] <= TODAY()
    )
RETURN
    // Power BI automatically applies forecasting algorithm
    // when this measure is used in a forecast visualization
    [Total Sales]

3. Power Query M Transformations

Clean Column Names

let
    Source = Csv.Document(File.Contents("data/GlobalSuperstore.csv")),
    PromotedHeaders = Table.PromoteHeaders(Source),
    CleanedNames = Table.TransformColumnNames(
        PromotedHeaders,
        each Text.Trim(_),
        each Text.Replace(_, " ", "")
    )
in
    CleanedNames

Parse Dates

let
    Source = #"Previous Step",
    ParsedDates = Table.TransformColumnTypes(
        Source,
        {
            {"OrderDate", type date},
            {"ShipDate", type date}
        }
    ),
    AddedCalendarColumns = Table.AddColumn(
        ParsedDates,
        "Year",
        each Date.Year([OrderDate]),
        Int64.Type
    )
in
    AddedCalendarColumns

Handle Missing Values

let
    Source = #"Previous Step",
    ReplacedNulls = Table.ReplaceValue(
        Source,
        null,
        0,
        Replacer.ReplaceValue,
        {"Discount", "Profit"}
    )
in
    ReplacedNulls

Create Geography Hierarchy

let
    Source = #"Previous Step",
    GroupedGeo = Table.Group(
        Source,
        {"Region", "Country", "State", "City", "PostalCode"},
        {{"Count", each Table.RowCount(_), Int64.Type}}
    ),
    SortedHierarchy = Table.Sort(
        GroupedGeo,
        {{"Region", Order.Ascending}, {"Country", Order.Ascending}}
    )
in
    SortedHierarchy

Configuration

Alert Thresholds

Modify alert thresholds by editing the ConfigThresholds.csv file or creating a DAX measure:

Profit Margin Threshold = 15  // %
Return Rate Threshold = 8     // %
Rolling Average Period = 90   // days

Refresh Schedule

Configure data refresh in Power BI Desktop:

File > Options and Settings > Options
Navigate to Data Load
Set refresh interval (recommended: daily at 02:00 UTC)

For Power BI Service deployment:

Publish dashboard to workspace
Go to Settings > Scheduled refresh
Set refresh frequency and time
Configure credentials for data source

Localization

Add language support by creating a translation table:

Translation Table = 
DATATABLE(
    "Key", STRING,
    "English", STRING,
    "Spanish", STRING,
    "French", STRING,
    {
        {"Total Sales", "Total Sales", "Ventas Totales", "Ventes Totales"},
        {"Profit", "Profit", "Beneficio", "Profit"},
        {"Region", "Region", "Región", "Région"}
    }
)

Then use in measures:

Localized Label = 
VAR UserLanguage = SELECTEDVALUE(Settings[Language])
RETURN
    SWITCH(
        UserLanguage,
        "Spanish", LOOKUPVALUE(Translation[Spanish], Translation[Key], "Total Sales"),
        "French", LOOKUPVALUE(Translation[French], Translation[Key], "Total Sales"),
        LOOKUPVALUE(Translation[English], Translation[Key], "Total Sales")
    )

Common Patterns

Pattern 1: Regional Drill-Down Analysis

Create a hierarchy for seamless drill-down:

// Create hierarchy in Geography table
Geography Hierarchy = 
    PATH(
        Geography[Region],
        Geography[Country],
        Geography[State],
        Geography[City]
    )

Add a matrix visual:

Rows: Geography Hierarchy
Values: [Total Sales], [Total Profit], [Profit Margin %]
Enable drill-down in visual settings

Pattern 2: Time Intelligence Comparisons

// Year-over-Year Growth
YoY Sales Growth = 
VAR CurrentYearSales = [Total Sales]
VAR PreviousYearSales = 
    CALCULATE(
        [Total Sales],
        SAMEPERIODLASTYEAR(Calendar[Date])
    )
RETURN
    DIVIDE(
        CurrentYearSales - PreviousYearSales,
        PreviousYearSales,
        0
    )

// Quarter-over-Quarter Growth
QoQ Sales Growth = 
VAR CurrentQuarterSales = [Total Sales]
VAR PreviousQuarterSales = 
    CALCULATE(
        [Total Sales],
        DATEADD(Calendar[Date], -1, QUARTER)
    )
RETURN
    DIVIDE(
        CurrentQuarterSales - PreviousQuarterSales,
        PreviousQuarterSales,
        0
    )

Pattern 3: Dynamic Top N Analysis

// Top 10 Products by Sales
Top 10 Products = 
VAR CurrentProductSales = [Total Sales]
VAR ProductRank = 
    RANKX(
        ALL(Product[ProductName]),
        [Total Sales],
        ,
        DESC,
        Dense
    )
RETURN
    IF(ProductRank <= 10, CurrentProductSales, BLANK())

Use in a bar chart with Product Name on axis and this measure as value.

Pattern 4: Exception Highlighting

// Flag underperforming regions
Region Performance Flag = 
VAR RegionMargin = [Profit Margin %]
VAR AvgMargin = 
    CALCULATE(
        [Profit Margin %],
        ALL(Geography[Region])
    )
VAR StdDev = 
    STDEVX.P(
        ALL(Geography[Region]),
        [Profit Margin %]
    )
RETURN
    IF(
        RegionMargin < AvgMargin - StdDev,
        "Underperforming",
        IF(
            RegionMargin > AvgMargin + StdDev,
            "Outperforming",
            "Normal"
        )
    )

Apply conditional formatting in table visuals based on this flag.

Pattern 5: Customer Segmentation

// RFM Score (Recency, Frequency, Monetary)
Customer RFM Score = 
VAR Recency = 
    DATEDIFF(
        MAX(Sales[OrderDate]),
        TODAY(),
        DAY
    )
VAR Frequency = COUNTROWS(Sales)
VAR Monetary = [Total Sales]
VAR RecencyScore = 
    SWITCH(
        TRUE(),
        Recency <= 30, 5,
        Recency <= 90, 4,
        Recency <= 180, 3,
        Recency <= 365, 2,
        1
    )
VAR FrequencyScore = 
    SWITCH(
        TRUE(),
        Frequency >= 50, 5,
        Frequency >= 25, 4,
        Frequency >= 10, 3,
        Frequency >= 5, 2,
        1
    )
VAR MonetaryScore = 
    SWITCH(
        TRUE(),
        Monetary >= 10000, 5,
        Monetary >= 5000, 4,
        Monetary >= 2000, 3,
        Monetary >= 1000, 2,
        1
    )
RETURN
    RecencyScore + FrequencyScore + MonetaryScore

Publishing & Sharing

Publish to Power BI Service

In Power BI Desktop:
- Click Home > Publish
- Select target workspace
- Wait for upload completion
Configure access:
- Open workspace in Power BI Service
- Click Access button
- Add users/groups with appropriate roles:
  - Viewer: Read-only access
  - Contributor: Can edit and publish
  - Admin: Full control
Embed in website (optional):

<iframe 
    width="800" 
    height="600" 
    src="https://app.powerbi.com/view?r=YOUR_REPORT_ID" 
    frameborder="0" 
    allowFullScreen="true">
</iframe>

Row-Level Security (RLS)

Implement region-based data filtering:

In Power BI Desktop, go to Modeling > Manage Roles
Create role:

// Role: RegionalManager
[Region] = USERNAME()

For email-based filtering:

// Role: EmailBasedAccess
VAR UserEmail = USERPRINCIPALNAME()
VAR UserRegion = 
    LOOKUPVALUE(
        UserMapping[Region],
        UserMapping[Email],
        UserEmail
    )
RETURN
    [Region] = UserRegion

Test role in Desktop:
- Modeling > View as Roles
- Select role and test
Assign roles in Service:
- Open dataset settings
- Navigate to Security
- Add users to roles

Troubleshooting

Issue: Data Refresh Fails

Symptoms: Scheduled refresh shows error in Power BI Service

Solutions:

Check data source credentials:
- Dataset settings > Data source credentials
- Re-enter credentials if expired
Verify file paths:
- In Power Query Editor, check File.Contents() paths
- Use relative paths or data gateway for cloud refresh
Enable data gateway (for on-premises data):

// Update source to use gateway
let
    Source = Sql.Database(
        "SERVER_NAME",
        "DATABASE_NAME",
        [Query="SELECT * FROM Sales"]
    )
in
    Source

Issue: Slow Dashboard Performance

Symptoms: Visuals take >5 seconds to load

Solutions:

Reduce data model size:

// Remove unnecessary columns in Power Query
let
    Source = #"Previous Step",
    RemovedColumns = Table.RemoveColumns(
        Source,
        {"Column1", "Column2"}
    )
in
    RemovedColumns

Optimize DAX measures:

// BAD: Uses ALL() unnecessarily
Slow Measure = 
CALCULATE(
    [Total Sales],
    ALL(Geography)
)

// GOOD: Uses ALLSELECTED() for context
Fast Measure = 
CALCULATE(
    [Total Sales],
    ALLSELECTED(Geography)
)

Use aggregations:
- Create pre-aggregated tables in Power Query
- Enable query folding when possible
Limit visual elements:
- Reduce number of visuals per page to <15
- Use bookmarks for alternative views

Issue: Forecast Not Displaying

Symptoms: Forecast line missing from visualization

Solutions:

Enable analytics pane:
- Select line chart visual
- Click Analytics pane (graph icon)
- Add Forecast
- Set forecast length (e.g., 12 months)
Ensure continuous date axis:

// Create full date range in Power Query
let
    StartDate = #date(2011, 1, 1),
    EndDate = Date.AddYears(Date.From(DateTime.LocalNow()), 1),
    DateRange = List.Dates(
        StartDate,
        Duration.Days(EndDate - StartDate),
        #duration(1, 0, 0, 0)
    ),
    ConvertedToTable = Table.FromList(
        DateRange,
        Splitter.SplitByNothing(),
        {"Date"}
    )
in
    ConvertedToTable

Issue: Incorrect Geographic Mapping

Symptoms: Map visual shows wrong locations

Solutions:

Set data category:
- Select geography column
- Column tools > Data category
- Choose appropriate type (City, State, Country, etc.)
Standardize location names:

// Clean and standardize state names
let
    Source = #"Previous Step",
    StandardizedStates = Table.ReplaceValue(
        Source,
        "CA",
        "California",
        Replacer.ReplaceText,
        {"State"}
    )
in
    StandardizedStates

Issue: Alert Flags Not Updating

Symptoms: Profit Alert measure shows outdated status

Solutions:

Force refresh of calculated columns:
- Modify measure slightly (add comment)
- Save and refresh data
Check threshold configuration:

// Debug: Show threshold value
Debug Threshold = 
VAR Threshold = 15
VAR CurrentMargin = [Profit Margin %]
RETURN
    "Threshold: " & Threshold & " | Current: " & CurrentMargin

Verify filter context:

// Add context awareness
Profit Alert Fixed = 
VAR ProfitMarginThreshold = 15
VAR CurrentMargin = [Profit Margin %]
VAR HasData = NOT(ISBLANK(CurrentMargin))
RETURN
    IF(
        HasData,
        IF(
            CurrentMargin < ProfitMarginThreshold,
            "🔴 Alert",
            "🟢 Healthy"
        ),
        BLANK()
    )

Advanced Techniques

Dynamic Measure Selection

Create a slicer that switches between different metrics:

Create parameter table:

Measure Selector = 
DATATABLE(
    "Metric", STRING,
    "MeasureID", INTEGER,
    {
        {"Total Sales", 1},
        {"Total Profit", 2},
        {"Profit Margin %", 3}
    }
)

Create dynamic measure:

Selected Metric = 
VAR SelectedID = SELECTEDVALUE('Measure Selector'[MeasureID])
RETURN
    SWITCH(
        SelectedID,
        1, [Total Sales],
        2, [Total Profit],
        3, [Profit Margin %],
        BLANK()
    )

Add slicer with Metric column and use [Selected Metric] in visuals

Custom Tooltips

Create a detailed tooltip page:

Create new page named "Tooltip - Product Detail"
Set page as tooltip: Page settings > Tooltip > ON
Add visuals showing product-specific metrics
Apply to main visual: Format > Tooltip > Report page > Tooltip - Product Detail

Best Practices

Use variables in DAX: Improves readability and performance
Avoid calculated columns when possible: Use measures instead
Create a date table: Essential for time intelligence
Document complex measures: Add comments explaining logic
Test with different date ranges: Ensure measures work across all periods
Use SELECTEDVALUE() for parameters: Handles multi-selection gracefully
Apply visual level filters: Reduces data volume in memory
Regular model optimization: Use Performance Analyzer to identify slow visuals

Resources

Official Power BI Documentation: https://docs.microsoft.com/power-bi/
DAX Guide: https://dax.guide/
Power Query M Reference: https://docs.microsoft.com/powerquery-m/
SQLBI (Advanced DAX patterns): https://www.sqlbi.com/

This skill covers the complete workflow for implementing, customizing, and deploying the SalesPulse 360 dashboard for retail analytics and business intelligence.

About this skill

Overview

Installation & Setup

Prerequisites

Initial Setup

Project Structure

Core Components

1. Data Model Architecture

2. Key DAX Measures

Total Sales

Total Profit

Profit Margin %

Sales vs Previous Period

Moving Annual Total (MAT)

Profit Alert Flag

12-Month Sales Forecast

3. Power Query M Transformations

Clean Column Names

Parse Dates

Handle Missing Values

Create Geography Hierarchy

Configuration

Alert Thresholds

Refresh Schedule

Localization

Common Patterns

Pattern 1: Regional Drill-Down Analysis

Pattern 2: Time Intelligence Comparisons

Pattern 3: Dynamic Top N Analysis

Pattern 4: Exception Highlighting

Pattern 5: Customer Segmentation

Publishing & Sharing

Publish to Power BI Service

Row-Level Security (RLS)

Troubleshooting

Issue: Data Refresh Fails

Issue: Slow Dashboard Performance

Issue: Forecast Not Displaying

Issue: Incorrect Geographic Mapping

Issue: Alert Flags Not Updating

Advanced Techniques

Dynamic Measure Selection

Custom Tooltips

Best Practices

Resources