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

# Extract the data archive
unzip data/global_superstore.zip -d data/
# Or use tar if provided as .tar.gz
tar -xzf data/global_superstore.tar.gz -C data/

# Open the main dashboard file
powershell -Command "Start-Process 'SalesPulse360.pbix'"
# Or simply double-click SalesPulse360.pbix in file explorer

// For local CSV
let
    Source = Csv.Document(File.Contents("C:\path\to\data\global_superstore.csv"), [Delimiter=",", Columns=24, Encoding=65001, QuoteStyle=QuoteStyle.None])
in
    Source

// For SQL Server (production)
let
    Source = Sql.Database(Environment.GetEnvironmentVariable("SQL_SERVER"), Environment.GetEnvironmentVariable("SQL_DATABASE")),
    dbo_Orders = Source{[Schema="dbo",Item="Orders"]}[Data]
in
    dbo_Orders

// In DAX, create or update the Settings table
Settings = 
DATATABLE(
    "Parameter", STRING,
    "Value", DOUBLE,
    {
        {"ProfitMarginThreshold", 0.15},
        {"ReturnRateWarning", 0.08},
        {"RollingAverageDays", 90},
        {"ForecastMonths", 12}
    }
)

// Total Sales
Total Sales = SUM(Orders[Sales])

// Total Profit
Total Profit = SUM(Orders[Profit])

// Profit Margin
Profit Margin = 
DIVIDE(
    [Total Profit],
    [Total Sales],
    0
)

// Moving Annual Total (MAT)
MAT Sales = 
CALCULATE(
    [Total Sales],
    DATESINPERIOD(
        'Calendar'[Date],
        MAX('Calendar'[Date]),
        -12,
        MONTH
    )
)

// Period-over-Period Growth
PoP Growth = 
VAR CurrentPeriod = [Total Sales]
VAR PreviousPeriod = 
    CALCULATE(
        [Total Sales],
        DATEADD('Calendar'[Date], -1, QUARTER)
    )
RETURN
DIVIDE(
    CurrentPeriod - PreviousPeriod,
    PreviousPeriod,
    0
)

// Profit Margin Alert (Sentinel System)
Profit Alert = 
VAR CurrentMargin = [Profit Margin]
VAR Threshold = LOOKUPVALUE(Settings[Value], Settings[Parameter], "ProfitMarginThreshold")
RETURN
IF(
    CurrentMargin < Threshold,
    "⚠️ Below Threshold",
    "✓ Healthy"
)

// Forecasted Sales (12-month projection)
Forecast Sales = 
VAR HistoricalData = 
    CALCULATETABLE(
        SUMMARIZE(
            Orders,
            'Calendar'[Date],
            "Sales", [Total Sales]
        ),
        DATESINPERIOD(
            'Calendar'[Date],
            MAX('Calendar'[Date]),
            -24,
            MONTH
        )
    )
VAR ForecastPeriod = 
    LOOKUPVALUE(Settings[Value], Settings[Parameter], "ForecastMonths")
RETURN
// Note: Power BI native forecasting uses built-in analytics pane
// For custom forecasting, integrate Python/R scripts or Azure ML

// Role: South Region Manager
[Region] = "South"

// Role: Multi-Region Director
[Region] IN {"South", "Central"}

// Role: Executive (all access)
1 = 1

# Power BI Service → Workspace → Dataset Settings → Security
# Add Azure AD users/groups to corresponding roles

# In Power BI Desktop
# File → Publish → Select workspace
# Or use keyboard shortcut: Alt+Shift+P

{
  "refreshSchedule": {
    "frequency": "Daily",
    "times": ["02:00"],
    "localTimeZoneId": "UTC",
    "notifyOnFailure": true
  },
  "gatewayConnection": {
    "dataSource": "OnPremisesDataGateway",
    "credentials": "UseOAuth2"
  }
}

// C# example: Push data to Power BI streaming dataset
using Microsoft.PowerBI.Api.V2;
using Microsoft.PowerBI.Api.V2.Models;

var credentials = new TokenCredentials(Environment.GetEnvironmentVariable("POWERBI_ACCESS_TOKEN"));
var client = new PowerBIClient(credentials);

var datasetId = Environment.GetEnvironmentVariable("POWERBI_DATASET_ID");
var rows = new List<object>
{
    new {
        OrderDate = DateTime.Now,
        Region = "West",
        Sales = 1250.50,
        Profit = 187.58
    }
};

await client.Datasets.PostRowsAsync(datasetId, "Orders", new PostRowsRequest(rows));

// Create hierarchy in Model View
Region Hierarchy = 
    'Geography'[Region] 
    → 'Geography'[Country] 
    → 'Geography'[City] 
    → 'Geography'[PostalCode]

// In visual interactions:
// Map visual → Drill mode → Click region to drill down

// Power Automate flow configuration
{
  "trigger": {
    "type": "PowerBI.DatasetRefreshCompleted",
    "datasetId": "$POWERBI_DATASET_ID"
  },
  "actions": [
    {
      "type": "Query",
      "query": "EVALUATE FILTER(Orders, [Profit Margin] < 0.15)",
      "condition": "RowCount > 0",
      "then": {
        "type": "Email",
        "to": "$ALERT_RECIPIENT_EMAIL",
        "subject": "⚠️ Profit Margin Alert",
        "body": "Low-margin orders detected. Review dashboard immediately."
      }
    }
  ]
}

// Create Translations table
Translations = 
DATATABLE(
    "Key", STRING,
    "English", STRING,
    "Spanish", STRING,
    "French", STRING,
    {
        {"Sales", "Sales", "Ventas", "Ventes"},
        {"Profit", "Profit", "Beneficio", "Bénéfice"},
        {"Region", "Region", "Región", "Région"}
    }
)

// Create language selector (Slicer visual)
Language = {"English", "Spanish", "French"}

// Dynamic measure for labels
Dynamic Label = 
VAR SelectedLang = SELECTEDVALUE(Language[Value], "English")
VAR Key = "Sales"
RETURN
SWITCH(
    SelectedLang,
    "English", LOOKUPVALUE(Translations[English], Translations[Key], Key),
    "Spanish", LOOKUPVALUE(Translations[Spanish], Translations[Key], Key),
    "French", LOOKUPVALUE(Translations[French], Translations[Key], Key),
    LOOKUPVALUE(Translations[English], Translations[Key], Key)
)

# Python script in Power BI Desktop (External Tools → Python)
import pandas as pd
import os

# Power BI automatically provides 'dataset' variable
df = dataset

# Apply filters (inherits from current dashboard state)
filtered = df[df['Region'] == 'West']

# Export
output_path = os.path.join(os.getenv('TEMP'), 'west_region_export.csv')
filtered.to_csv(output_path, index=False)

print(f"Exported {len(filtered)} rows to {output_path}")

<!-- Embed Power BI report in custom HTML -->
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SalesPulse 360</title>
    <style>
        #reportContainer {
            width: 100%;
            height: 100vh;
            border: none;
        }
    </style>
</head>
<body>
    <iframe 
        id="reportContainer"
        src="https://app.powerbi.com/reportEmbed?reportId=YOUR_REPORT_ID&autoAuth=true"
        frameborder="0" 
        allowFullScreen="true">
    </iframe>

    <script src="https://cdn.jsdelivr.net/npm/powerbi-client@2.19.0/dist/powerbi.min.js"></script>
    <script>
        const embedConfig = {
            type: 'report',
            id: process.env.POWERBI_REPORT_ID,
            embedUrl: `https://app.powerbi.com/reportEmbed?reportId=${process.env.POWERBI_REPORT_ID}`,
            accessToken: process.env.POWERBI_EMBED_TOKEN,
            tokenType: models.TokenType.Embed,
            settings: {
                panes: {
                    filters: { expanded: false, visible: true },
                    pageNavigation: { visible: true }
                },
                background: models.BackgroundType.Transparent
            }
        };

        const reportContainer = document.getElementById('reportContainer');
        const powerbi = new pbi.service.Service(pbi.factories.hpmFactory, pbi.factories.wpmpFactory, pbi.factories.routerFactory);
        const report = powerbi.embed(reportContainer, embedConfig);

        report.on('loaded', () => console.log('Report loaded'));
        report.on('error', (event) => console.error('Error:', event.detail));
    </script>
</body>
</html>

// Node.js backend for token generation
const msal = require('@azure/msal-node');
const axios = require('axios');

const msalConfig = {
    auth: {
        clientId: process.env.AZURE_CLIENT_ID,
        clientSecret: process.env.AZURE_CLIENT_SECRET,
        authority: `https://login.microsoftonline.com/${process.env.AZURE_TENANT_ID}`
    }
};

const cca = new msal.ConfidentialClientApplication(msalConfig);

async function getEmbedToken(reportId) {
    const tokenRequest = {
        scopes: ['https://analysis.windows.net/powerbi/api/.default']
    };
    
    const response = await cca.acquireTokenByClientCredential(tokenRequest);
    const accessToken = response.accessToken;

    const embedUrl = `https://api.powerbi.com/v1.0/myorg/reports/${reportId}/GenerateToken`;
    const embedResponse = await axios.post(embedUrl, 
        { accessLevel: 'View' },
        { headers: { 'Authorization': `Bearer ${accessToken}` }}
    );

    return embedResponse.data.token;
}

// Power Query: Merge Salesforce data
let
    SalesforceSource = Salesforce.Data("https://instance.salesforce.com", [ApiVersion = "v52.0"]),
    Opportunities = SalesforceSource{[Name="Opportunity"]}[Data],
    JoinedData = Table.NestedJoin(
        Orders, {"CustomerID"},
        Opportunities, {"AccountId"},
        "OpportunityData",
        JoinKind.LeftOuter
    )
in
    JoinedData

# Download .pbiviz file from AppSource
# Power BI Desktop → Insert → Get more visuals → Import from file

{
  "visualType": "hierarchySlicer",
  "config": {
    "levels": ["Region", "Country", "City"],
    "expandAll": false,
    "singleSelect": true
  }
}