Datanalytics

6.1-DashBoard Creation with Power Pivot, Power Query and DAX

7 min read

2025-09-29 15:57

Status: Child

Tags: Data Analysis Excel Power-Pivot Power Query

Excel Dashboard Creation: Sales and Profit Analysis

Session Overview

This session covers the complete process of creating an interactive sales and profit dashboard in Excel using pivot tables and slicers. The dashboard provides comprehensive visualization of sales data across multiple dimensions including time periods, categories, states, and customers.

Dashboard Components

The dashboard will include the following key elements:

  • Category, Year, and Month filters
  • Total Sales and Total Profit KPIs
  • Profit by Year visualization
  • Sales by Category breakdown
  • Customer Count by Year
  • Sales by State (Map visualization)
  • Top 5 Customers analysis
  • Sales by Month trend

Data Preparation

Step 1: Setting Up the Calculation Table

Begin by creating a dedicated calculation table from the raw data:

  1. Copy the raw data
  2. Create a new table named “Calculation”
  3. Include essential columns: Sales, Quantity, Profit, Math, and Year
  4. Save the file with an appropriate name (e.g., “Sales and Profit Dashboard”)

Step 2: Creating Measure Calculations

Create the following measures using pivot tables:

Total Sales and Total Profit

  • Insert a pivot table for Total Sales
  • Insert a separate pivot table for Total Profit

Profit by Year

  • Create a matrix showing profit trends across years
  • Configure the pivot table with Year in rows and Profit values

Profit by Subcategory

  • Set up a matrix with Subcategory in rows
  • Display profit values for each subcategory

Sales by Subcategory

  • Similar to profit by subcategory
  • Replace profit values with sales values

Customer Count by Year

  • Use the customer column from raw data
  • Apply COUNT function to determine unique customers per year
  • Create a year-wise breakdown of customer acquisition

Sales by State

  • Prepare data for map visualization
  • Include State and Sales columns
  • This will be used to create a geographical map chart

Top 5 Customers

  • Create a pivot table with Customer and Profit
  • Apply Top 10 filter, set to display top 5
  • Sort in descending order by profit

Sales by Month

  • Create a monthly sales breakdown
  • Remove unnecessary filters (Quarter, Order Date)
  • Keep only Month and Sales data

Dashboard Design

Layout Setup

  1. Create the Dashboard Sheet

    • Insert a new sheet for the dashboard
    • Enable gridlines for easier alignment
    • Set up the canvas area

  2. Background Design

    • Select the working area
    • Navigate to Home Ribbon
    • Apply light gray color for base
    • Insert rectangles for design elements
    • Use Shape Fill, Shape Outline, and Shape Effects for styling
    • Apply shadow effects (second shadow option)
    • Create 3D effects with darker colors where appropriate

  3. Title Section

    • Insert a text box
    • Add title: “Sales and Profit Dashboard”
    • Format with white text color
    • Set shape outline to none
    • Add appropriate icons (consider profit/sales icons)
    • Change icon colors using Graphics Format > Graphics Filter > White

Visual Elements Creation

KPI Cards (Total Sales and Total Profit)

  1. Insert icons representing sales and profit
  2. Create text boxes for labels
  3. Link cells to calculation measures
  4. Format with bold text
  5. Apply appropriate color schemes (black text recommended for clarity)

Profit by Year Chart

  1. Create a 2D bar chart
  2. Remove grid lines
  3. Add data labels
  4. Adjust gap width for better visualization
  5. Format chart:
    • Shape Fill: No Fill
    • Apply gradient color with white tones
    • Remove chart borders

Sales by Subcategory (Funnel Chart)

  1. Navigate to Insert Chart > All Charts
  2. Select Funnel Chart from recommendations
  3. Sort data in ascending or descending order
  4. Format the chart:
    • Shape Fill: No Fill
    • Double-click chart area for formatting options
    • Apply gradient color scheme (black gradient recommended)

Customer Count by Year (Pie Chart)

  1. Insert a pie chart
  2. Hide all unnecessary chart elements
  3. Add data labels
  4. Remove chart border (Shape Fill: No Fill)
  5. Apply appropriate gradient or solid colors

Sales by State (Map Chart)

  1. Select the sales by state data
  2. Insert a Map chart
  3. Position appropriately on dashboard
  4. Format:
    • Shape Fill: No Fill
    • Consider solid fill or gradient for better aesthetics

Note: Map functionality works best with recognized geographical entities. For Bangladesh divisions or districts, ensure proper naming conventions match Excel’s geographical database.

Top 5 Customers Chart

  1. Create a 2D bar chart
  2. Add data labels
  3. Remove unnecessary elements
  4. Format with Shape Fill: No Fill
  5. Apply consistent color scheme

Sales by Month (Line/Column Chart)

  1. Create appropriate chart type for monthly trends
  2. Ensure January through December are displayed
  3. Format:
    • Shape Fill: No Fill
    • Remove unnecessary gridlines and elements

Adding Interactivity with Slicers

Creating Slicers

  1. Click on any pivot table

  2. Navigate to Insert > Slicer

  3. Select the following dimensions:

    • Category
    • Month
    • Year

  4. Position slicers appropriately on the dashboard

Connecting Slicers to All Pivot Tables

Critical Step: By default, slicers only connect to the pivot table from which they were created.

To connect slicers to all visualizations:

  1. Right-click on each slicer
  2. Select “Report Connections”
  3. Check all pivot tables in the connection list
  4. Repeat for each slicer (Category, Month, Year)

This ensures that selecting any filter updates all dashboard elements simultaneously.

Why Use Specific Categories for Slicers?

  • Category: Contains only 3 unique values (Office Supply, Furniture, Technology), making it manageable
  • Year: Typically contains 3-4 values
  • Month: Contains 12 values (January-December)

These dimensions are chosen because they have a reasonable number of unique values. Columns with too many unique entries (like Customer IDs) would create unwieldy slicers.

Dashboard Behavior

Dynamic Updates

The dashboard is fully dynamic:

  • Selecting a category filters all visualizations
  • Choosing a specific month updates all metrics
  • Year selection modifies all relevant charts
  • Multiple filters can be combined for detailed analysis

Data Refresh

If new data is added to the raw data table:

  1. Refresh all pivot tables
  2. All dashboard elements will automatically reflect the updated data
  3. No manual recalculation needed

Technical Notes

VBA and Macros

While VBA (Visual Basic for Applications) can be used for automation in Excel:

  • Current implementation uses native Excel functionality
  • VBA is useful for complex automation scenarios
  • Modern alternatives exist, but VBA remains relevant for Excel-specific tasks
  • The demonstrated approach avoids VBA to maintain simplicity

Power Pivot and Power Query

Question raised: How to implement similar dashboards using Power Pivot?

Answer:

  • The current demonstration uses classical pivot tables
  • Power Pivot integration provides additional capabilities for larger datasets
  • Future sessions will cover Power Pivot dashboard creation
  • Two different approaches will be demonstrated for comparison

Map Visualization Limitations

  • Excel maps work well with recognized geographical entities (countries, US states)
  • Bangladesh divisions may have limited support
  • Some divisions (like Mymensingh) may not appear due to database updates
  • The feature requires proper geographical naming conventions

Best Practices

  1. Keep slicers simple: Use dimensions with limited unique values
  2. Consistent formatting: Apply uniform color schemes and styles
  3. Clear labeling: Ensure all charts have descriptive titles
  4. Connect all slicers: Don’t forget to link slicers to all pivot tables
  5. Test interactivity: Verify that all filters work correctly across visualizations
  6. Consider color schemes: Use gradients and shadows for professional appearance
  7. Maintain data structure: Keep raw data organized in the calculation table

Common Issues and Solutions

Slicer Not Affecting All Charts

Problem: Clicking a slicer only updates some visualizations.

Solution:

  • Right-click the slicer
  • Select “Report Connections”
  • Ensure all pivot tables are checked
  • Apply connections to each slicer individually

Map Not Showing Correctly

Problem: Geographic data not displaying on map.

Solution:

  • Verify location names match Excel’s geographical database
  • Use standard naming conventions (e.g., state abbreviations for US states)
  • For non-standard regions, consider alternative visualization methods

Next Steps

  1. Practice: Work with the provided file to recreate the dashboard
  2. Experiment: Try different chart types and color schemes
  3. Next Session: Advanced dashboard creation with Power Pivot and Power Query integration
  4. Share: Consider sharing completed dashboards on LinkedIn for portfolio building

Conclusion

This session provided a comprehensive walkthrough of creating a professional, interactive sales and profit dashboard in Excel. The dashboard combines multiple visualization types with dynamic filtering capabilities, enabling effective business intelligence and decision-making. The techniques demonstrated can be adapted for various business scenarios and datasets.

References

Backlinks