Categories: Data Analytics / Power BI
Tags:

Sales vs. Budget Analysis Dashboard in Power BI

On Star & Snowflake Schema

Creating a Sales vs. Budget Analysis Dashboard in Power BI based on snowflake schema is a great idea. Here’s a structured approach with visual ideas, KPIs, and filters to help you build a powerful and insightful dashboard:

✅ Key Objective

Compare actual Sales vs Budget across multiple dimensions like Product Category, Customer Region, Campaign, Date, etc.

🔷 KPIs to Show at the Top (Card Visuals)

  1. Total Sales (Revenue)
    SUM(Sales[Unit Price] * Sales[Units])
  2. Total Budget
    SUM(Budget[Value])
  3. Variance (Sales – Budget)
    Sales - Budget
  4. % Variance
    (Sales - Budget) / Budget * 100
  5. Units Sold vs Units Budgeted (if units exist in budget too)

📘 Optional: Add % Variance

To calculate the percentage variance, use:

% Variance = 
DIVIDE([Variance], [Total Budget], 0)

This will give you how far actual sales are above or below budget in percentage terms.

3. Gross Margin

Helps assess profitability per unit.

Gross Margin = SUMX(sales, (sales[Unit Price] - sales[Unit Cost]) * sales[Units])

4. Unit Cost vs Unit Price % (Cost Ratio)

Shows what % of your price is eaten up by cost.

Cost Ratio (%) = 
DIVIDE(
    SUM(sales[Unit Cost]),
    SUM(sales[Unit Price]),
    0
) * 100


5. Cost Variance

If budgeted cost is available, calculate variance against it.

Cost Variance = SUM(sales[Unit Cost]) - [Budgeted Unit Cost]
6. Profit per Unit

Net profit per unit sold.

Profit per Unit = AVERAGE(sales[Unit Price] - sales[Unit Cost])

📊 Visuals to Include

1. Sales vs Budget Over Time (Line or Area Chart)

  • X-axis: Date[Month] or Date[Quarter]
  • Y-axis: Sales & Budget
  • Show trends side by side

2. Sales vs Budget by Product Category (Bar/Column Chart)

  • Axis: categorysegment[Category]
  • Values: Total Sales & Total Budget
  • Optional: Use diverging bars for variance

3. Sales vs Budget by Region or Country (Map or Bar Chart)

  • Use address[Country] or Region
  • Add tooltip with variance %

4. Top Performing Campaigns (Bar or Donut Chart)

  • Show top campaign[CampaignID] by Sales
  • Add Budget overlay

5. Sales vs Budget Matrix/Table

  • Rows: Product, Customer, or Region
  • Columns: Sales, Budget, Variance, % Variance
  • Use conditional formatting to highlight over/under budget

📅 Time Intelligence Ideas

Add time slicers or filters:

  • Date[Year], Date[Quarter], Date[Month]

Use DAX measures:

Sales Amount = SUMX(sales, sales[Units] * sales[Unit Price])
Budget Amount = SUM(budget[Value])
Variance = [Sales Amount] - [Budget Amount]
Variance % = DIVIDE([Variance], [Budget Amount])

🔍 Filters/Slicers to Add

  • Product Category
  • Country / Region
  • Campaign
  • Manufacturer
  • Date Range

🟨 Optional Advanced Ideas

  1. Trend Arrows: Show up/down arrows next to KPIs based on target
  2. Forecasting: Use Power BI forecasting on time-based visuals
  3. Bookmarks: Switch between different views (e.g., Product vs Region)
  4. Drill-through Pages: Click on a product to see region/campaign performance