power-bi-salespulse-360-dashboard

# Power BI SalesPulse 360 Dashboard Skill > Skill by [ara.so](https://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](https://powerbi.microsoft.com/desktop/)) - **Global Superstore dataset** (included in repository as CSV) - Minimum 8GB RAM for smooth performance ### Initial Setup 1. **Clone the repository:** ```bash git clone https://github.com/MahbubNibir/power-bi-retail-analytics-viz.git cd power-bi-retail-analytics-viz ``` 2. **Extract the dataset:** ```bash # Extract the Global Superstore CSV from the data archive unzip 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 ```dax Total Sales = SUM(Sales[Sales Amount]) ``` #### Total Profit ```dax Total Profit = SUM(Sales[Profit]) ``` #### Profit Margin % ```dax Profit Margin % = DIVIDE( [Total Profit], [Total Sales], 0 ) * 100 ``` #### Sales vs Previous Period ```dax 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) ```dax MAT Sales = CALCULATE( [Total Sales], DATESINPERIOD( Calendar[Date], LASTDATE(Calendar[Date]), -12, MONTH ) ) ``` #### Profit Alert Flag ```dax Profit Alert = VAR ProfitMarginThreshold = 15 VAR CurrentMargin = [Profit Margin %] RETURN IF( CurrentMargin < ProfitMarginThreshold, "🔴 Alert", IF( CurrentMargin < ProfitMarginThreshold + 5, "🟡 Caution", "🟢 Healthy" ) ) ``` #### 12-Month Sales Forecast ```dax 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 ```m 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 ```m 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 ```m let Source = #"Previous Step", ReplacedNulls = Table.ReplaceValue( Source, null, 0, Replacer.ReplaceValue, {"Discount", "Profit"} ) in ReplacedNulls ``` #### Create Geography Hierarchy ```m 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: ```dax 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: ```dax 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: ```dax 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: ```dax // 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 ```dax // 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 ```dax // 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 ```dax // 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 ```dax // 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):** ```html <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:** ```dax // Role: RegionalManager [Region] = USERNAME() ``` For email-based filtering: ```dax // Role: EmailBasedAccess VAR 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):** ```m // Update source to use gateway let Source = Sql.Database( "SERVER_NAME", "DATABASE_NAME", [Query="SELECT * FROM Sales"] ) in Source ``` ### Issue: Slow Dashboard Performance