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,