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

1. Clone the repository:

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

2. Extract the dataset:

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

3. Open the dashboard:

   • Launch Power BI Desktop
   • Open SalesPulse360.pbix
   • Wait for initial data transformation (2-3 minutes)

4. 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 = 15VAR 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:

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

For Power BI Service deployment:

1. Publish dashboard to workspace
2. Go to Settings > Scheduled refresh
3. Set refresh frequency and time
4. 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 tableGeography 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 GrowthYoY Sales Growth = VAR CurrentYearSales = [Total Sales]VAR PreviousYearSales =     CALCULATE(        [Total Sales],        SAMEPERIODLASTYEAR(Calendar[Date])    )RETURN    DIVIDE(        CurrentYearSales - PreviousYearSales,        PreviousYearSales,        0    )
// Quarter-over-Quarter GrowthQoQ 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 SalesTop 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 regionsRegion 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

1. In Power BI Desktop:

   • Click Home > Publish
   • Select target workspace
   • Wait for upload completion

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

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

1. In Power BI Desktop, go to Modeling > Manage Roles

2. Create role:

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

For email-based filtering:

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

3. Test role in Desktop:

   • Modeling > View as Roles
   • Select role and test

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

1. Check data source credentials:

   • Dataset settings > Data source credentials
   • Re-enter credentials if expired

2. Verify file paths:

   • In Power Query Editor, check File.Contents() paths
   • Use relative paths or data gateway for cloud refresh

3. Enable data gateway (for on-premises data):

// Update source to use gatewaylet    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:

1. Reduce data model size:

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

2. Optimize DAX measures:

// BAD: Uses ALL() unnecessarilySlow Measure = CALCULATE(    [Total Sales],    ALL(Geography))
// GOOD: Uses ALLSELECTED() for contextFast Measure = CALCULATE(    [Total Sales],    ALLSELECTED(Geography))

3. Use aggregations:

   • Create pre-aggregated tables in Power Query
   • Enable query folding when possible

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

1. Enable analytics pane:

   • Select line chart visual
   • Click Analytics pane (graph icon)
   • Add Forecast
   • Set forecast length (e.g., 12 months)

2. Ensure continuous date axis:

// Create full date range in Power Querylet    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:

1. Set data category:

   • Select geography column
   • Column tools > Data category
   • Choose appropriate type (City, State, Country, etc.)

2. Standardize location names:

// Clean and standardize state nameslet    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:

1. Force refresh of calculated columns:

   • Modify measure slightly (add comment)
   • Save and refresh data

2. Check threshold configuration:

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

3. Verify filter context:

// Add context awarenessProfit Alert Fixed = VAR ProfitMarginThreshold = 15VAR 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:

1. Create parameter table:

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

2. Create dynamic measure:

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

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

Custom Tooltips

Create a detailed tooltip page:

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

Best Practices

1. Use variables in DAX: Improves readability and performance
2. Avoid calculated columns when possible: Use measures instead
3. Create a date table: Essential for time intelligence
4. Document complex measures: Add comments explaining logic
5. Test with different date ranges: Ensure measures work across all periods
6. Use SELECTEDVALUE() for parameters: Handles multi-selection gracefully
7. Apply visual level filters: Reduces data volume in memory
8. 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.