Data Insights and Dashboard Creation

Introduction to Module 2

This module transitions from storing and structuring data (Module 1) to understanding, visualizing, and telling a story with data. The goal: turn raw data into business insights through interactive dashboards.

What you will learn

The core philosophy: Dashboards for decisions

A dashboard is not a collection of charts — it is a tool that helps a manager answer specific business questions. Every element on the dashboard should serve a decision.

Dashboards are not about creating charts. They're about decisions.

Decision questions a good dashboard answers

A well‑designed dashboard enables quick answers to questions like:

How the module unfolds

1. Pivot tables & pivot charts – understand the mechanics.
2. Build a sales dashboard using a vegetable‑delivery app dataset.
3. Analyze cluster‑wise sales – uncover top and weak regions.
4. Examine customer behavior – demand patterns and return volumes.
5. Compile everything into a clean, professional dashboard.

Exam tip: Memorise the four sample business questions above — they are typical of what a dashboard should answer. Expect a question that asks you to match a dashboard element to a decision.

Key takeaways

• This module moves from data storage (Module 1) to data insight via visualisation.
• Pivot tables and pivot charts are the core tools for interactive dashboards.
• Dashboards are decision‑centric, not chart‑centric.
• Real example used: a vegetable delivery app (sales, cluster, returns, customer behaviour).
• By the end, you can build a professional dashboard in Excel or Google Sheets with filters and slicers.

Pivot Tables - I

A pivot table is a spreadsheet feature that automatically reorganises and summarises selected columns and rows from a raw data table into a compact, flexible report. It does not change the original data — it creates a new summary view. Technically, a pivot table is a form of cross tabulation; it gets its name because you can pivot (rotate) the summary around different variables. With drag-and-drop fields, you can instantly group thousands of rows by product, city, date, customer, etc., and switch between monthly totals, category breakdowns, or top-selling SKUs.

Main Components

A pivot table is built from four core areas:

Definition (from Wiki): “A pivot table is a table of values which are aggregations of groups of individual values from a more extensible table.” — it takes one detailed table and produces a summary view.

Pivot Charts

Once a pivot table is ready, you can build a chart on that pivot data – a pivot chart. This is a dynamic visual representation of the pivot table. In short: pivot tables summarise; pivot charts visualise.

Worked Example: Vegetable Sales Data

A small dataset of vegetable sales across three cities contains: Date, City, Product (Potato/Tomato), Quantity, Sale Price, and Total Value.

Steps to create a pivot table (Excel):

1. Select the dataset → Insert → Pivot Table.
2. Choose location (existing sheet or new worksheet).
3. The PivotTable Fields pane appears with all column headers.
4. Drag Product into Rows, City into Columns, and Quantity into Values.

Result (first orientation – product rows, city columns):

By swapping rows and columns (or adding filters), you can instantly re-orient the summary — e.g., city as rows, product as columns — to view total sales per city.

Modifying calculations: By default, pivot tables sum numeric values. You can change the aggregation by opening Value Field Settings and choosing Count, Average, Max, Min, etc.

Exam tip: The Value Field Settings menu is where you switch between aggregation functions (Sum, Average, Count, Max, Min). Forgetting this is the most common reason a pivot table gives unexpected numbers.

Key Takeaways

• A pivot table summarises large datasets without altering the raw data — it creates a new aggregated view.
• The four main components: Rows, Columns, Values, Filters.
• Drag fields into these areas to instantly group and summarise.
• Default aggregation for numeric fields is Sum; use Value Field Settings to change it.
• A pivot chart is a dynamic chart linked to a pivot table – summary + visualisation.
• Pivoting (swapping rows and columns) lets you quickly explore different perspectives of the same data.

Understanding the Data Before Pivoting

Real analysis starts with understanding what the data represents, its granularity, and how sheets relate.

Two sheets in the workbook:

1. SKU Sales Trends – daily SKU-level summary data (dates Jan–Mar 2018, ~700 rows). Columns: Date, SKU Name, SKU Total Orders, Vegetable Orders, Fruit Orders, Sale Price, Billed Tonnage, Return Tonnage.
2. Customer Level SKU – transaction-level data (Feb only, different SKUs, includes Customer ID, Locality, Quantity, etc.). Not directly related to the first sheet.

SKU = Stock Keeping Unit – the smallest inventory unit (e.g., tomatoes: Standard vs. Hybrid are two SKUs).

Key observation: The SKU Sales Trends table is already aggregated at a daily level. Values like “Total Orders” are attributes of the date, not the SKU. These values repeat across all SKUs for the same date. Therefore, when building a pivot, Sum would double-count – use Max, Min, or Average instead.

Decomposing Order Types Using Set Theory

From the daily summary:

• $|U| = \text{Total Orders}$ (all orders)
• $|V| = \text{Vegetable Orders}$ (orders with ≥1 vegetable)
• $|F| = \text{Fruit Orders}$ (orders with ≥1 fruit)

These sets overlap (an order can contain both). To find exclusive categories:

$$\begin{aligned} |V \cap F| &= |V| + |F| - |U| \\ \text{Only Veg} &= |V| - |V \cap F| \\ \text{Only Fruit} &= |F| - |V \cap F| \end{aligned}$$

Example (one day): Total = 1324, Veg = 1289, Fruit = 702 $|V \cap F| = 1289 + 702 - 1324 = 667$ Only Veg = $1289 - 667 = 622$ Only Fruit = $702 - 667 = 35$

These derived columns are computed directly in Excel (or any analysis tool) and added to the source table.

Building the Pivot Table & Chart

Steps:

1. Select the data table (including new columns).
2. Insert Pivot Table, choose new worksheet, optionally check “Add this data to Data Model”.
3. Set Rows = Date (un-group from months to show days).
4. Add Values for: Total Orders, Only Veg, Only Fruit, Both. Change each to Max (or Min – identical per date).
5. Insert Pivot Chart (Line chart) from the pivot.
6. Add trend lines (linear) to key series.

Insight from the chart:

• All categories grow over time.
• The “Both Veg & Fruit” order count has a steeper slope than “Only Veg” or “Only Fruit”.
• This suggests customers are increasingly placing orders that contain both food types — a cross-selling opportunity.

Exam tip: When source data is pre-aggregated (e.g., daily totals repeated per SKU), using Sum in a pivot table creates inflated values. Always check aggregation level and use Max/Min/Count if duplication exists.

Key Takeaways

• Understand data granularity: SKU Sales Trends is daily-summary, not transactional; the second sheet is different and unrelated.
• SKU = smallest inventory unit; classification (essentials, fruits, etc.) can be used in analysis.
• Use set theory ($|A \cup B| = |A| + |B| - |A \cap B|$) to decompose overlapping categories into exclusive counts.
• Pivot tables on pre-aggregated data require Max (or Min, Avg) instead of Sum.
• Pivot charts with trend lines reveal growth rates; steeper slope = faster growth segment.
• The insight “Both Veg & Fruit orders are the main growth driver” informs cross-selling strategy.

Pivot Tables – Aggregating Percentages and Week-Level Summaries

Percentages standardize quantities so you can compare across time periods or categories without being misled by raw numbers. For example, raw daily order quantities fluctuate, but converting them to percentages of total orders reveals relative trends.

Adding Percentage Columns in a Pivot Table

From the raw order data (SKU-level), create calculated columns:

• % Both Veggies & Fruits = (Both Veggie & Fruit Orders) / Total Orders
• % Only Veggies = (Only Veggie Orders) / Total Orders
• % Only Fruits = (Only Fruit Orders) / Total Orders

These three sum to 100% and make trends visible. In the example, over time:

• % Both rose from 38% → 50%
• % Only Veggies dropped from 60% → 47%
• % Only Fruits grew from 0% → 3%

This is far clearer than raw counts.

The Granularity Trap: Why You Cannot Reuse Day-Level Percentages for Week-Level Summaries

Directly summing or averaging daily percentages across a week produces a meaningless metric. Percentages are computed relative to the denominator of that row. At a different granularity (e.g., week), the base changes (total orders across the week vs. per day).

Correct Workflow for Week-Level Percentages

1. Base data – raw daily order table (with date)
2. Add a WeekNumber column (extracted from date using WEEKNUM())
3. First pivot table – aggregate raw counts by week:
   • Rows: WeekNumber
   • Values (all using Max because values are identical across rows within the same week):
     • Total Orders
     • Only Veggie Orders
     • Only Fruit Orders
     • Both Veggie & Fruit Orders
   • Why Max? Each day's row already holds the daily total; we want the daily value itself, not a sum (which would sum across all days in the week incorrectly). Since it's the same value for each product within that day, Max (or Min or Average) returns the correct daily number. Then the pivot table's implicit sum across days gives the weekly total.
4. Second pivot table (on top of the first pivot table’s data) – compute percentages:
   • Rows: WeekNumber
   • Values:
     • Total Orders (as raw number)
     • Only Veggies (sum across weeks, but we will compute % later)
     • Only Fruits
     • Both Veggies & Fruits
   • Add calculated columns:
     • % Both = Both / Total
     • % Only Veggies = Only Veggies / Total
     • % Only Fruits = Only Fruits / Total
5. Chart – insert a pivot chart from this table to visualize week-on-week percentage trends.

Exam tip: When shifting granularity (e.g., daily → weekly), never reuse percentage fields from the finer level. Always re-aggregate the raw counts at the new level and then compute percentages. The same principle applies for any ratio metric (averages, rates, etc.).

Why Max (or Min) Works for This Aggregation

In the first pivot table (daily data, one row per product per day), each day’s total order count is the same for every product row within that day. Using Max picks that single value (or Sum would multiply it by the number of products, giving a huge inflated number). The pivot table’s row label (WeekNumber) then groups all rows in that week, and the pivot table’s implicit sum of the daily Max values gives the correct weekly total.

If you instead used Sum on the raw daily number, you would sum across all products every day, double- and triple-counting the same daily total. Always check your aggregation function matches the data’s grain.

Key takeaways

• Percentages standardize data for trend analysis across time.
• Granularity mismatch – do not reuse percentage fields at a coarser level; re-aggregate raw numbers first.
• Correct hierarchy: raw data → pivot with raw counts at desired grain → new pivot with percentages.
• Use Max (or Min, Average) when a value is identical across rows within a day to avoid double-counting.
• Generate GetPivotData should be turned off (PivotTable Analyze → Options → uncheck “Generate GetPivotData”) when referencing cells directly in the second pivot’s calculated columns.

Pivot Tables IV: Measures and Slicers

When you add a calculated field (a column with a formula) outside a pivot table – for example, a percentage column derived from the pivot’s values – you create a static copy. If you later filter the pivot, the external formula can break (e.g., division by zero when the denominator becomes empty). The better approach: embed calculations directly inside the pivot using measures.

Drawback of External Calculations

• External formulas reference specific cells; when the pivot reshapes (filtering, adding fields), those references may produce errors (e.g., #DIV/0!).
• To avoid errors, you could copy the pivot as values (static), but then you lose the ability to dynamically filter or update.

Solution: Measures in Pivot Tables

A measure is a DAX (Data Analysis Expressions) formula stored inside the pivot. It recalculates automatically as the pivot changes – no errors, no stale links.

How to Add a Measure

1. Right-click anywhere within the pivot table.
2. Select Add Measure (or Add a Measure).
3. Give the measure a name (e.g., % Veggie & Fruit).
4. Write the formula using field references: = SUM( 'Max'[Veggie] ) + SUM( 'Max'[Fruit] ) divided by SUM( 'Max'[Total Orders] ) (or whatever aggregate is needed).
5. Click OK – the measure appears in the Values area.

Example: Percentage Measures

Create these three measures:

These measures can then be added to the Values area alongside or instead of raw counts. Filtering the pivot now works seamlessly – no divide‑by‑zero errors because the formula respects the current filter context.

Building Charts with Measures

• After adding measures, create a PivotChart from the same pivot table (Insert > PivotChart, choose a line chart).
• Remove unwanted series (e.g., raw sums) and keep only the measure fields.
• For week-by-week analysis, place Week Number in the Rows area, not in Values.

The result: a clean line chart showing, for example, the weekly percentage distribution of veggie and fruit orders.

Exam tip: When you need to filter a pivot but also show normalized values (percentages), always use measures rather than calculated columns outside the pivot. Measures respect the filter context and prevent #DIV/0! errors.

Using Slicers for Interactive Filtering

A slicer provides a visual filter (buttons) that connects to one or more pivot tables/charts.

• Insert a slicer from the PivotTable Analyze tab > Insert Slicer.
• Choose the field to filter on (e.g., Week Number).
• The slicer can be placed on a dashboard sheet.

Limitation: One Slicer per Pivot Table

A slicer can only filter pivot tables that share the same source pivot table (or the same underlying data model). If two charts come from different pivot tables (e.g., one uses Week Number, another uses Date), a single slicer cannot control both simultaneously.

• Two charts use different pivots: one uses Week Number (measures), while the other uses Date (raw data). A single slicer cannot link to both because they do not share the same pivot source.
• To make a slicer work across multiple charts, ensure they are all derived from the same pivot table.

Practical Example: Adding Revenue Analysis

Using the same sales dataset, you can create another pivot:

1. Insert a new pivot table (existing worksheet).
2. Place Week Number in Rows, SKU Classification in Columns, and Sum of Revenue in Values.
3. Insert a line PivotChart to view weekly revenue trends per product category.
4. Optionally, add other measures like Average Sale Price or Bulk Tonnage (be careful to use the correct aggregation – e.g., AVERAGE for price).

Note: When adding measures from different fields, verify the source pivot table. Referencing a different pivot for Average Price, for example, mixes aggregations and breaks consistency.

---

Key Takeaways

• External calculations on pivot values break when the pivot is filtered – use measures instead.
• Measures are DAX formulas stored inside the pivot; they automatically adapt to filters.
• To build a chart with percentages, create a measure (e.g., % Veggies = SUM(Veggies)/SUM(Total)) and use it in a PivotChart.
• Slicers provide interactive filtering but only work across charts that share the same pivot table.
• To avoid filtering issues, design all charts from one pivot table or use a common data model (Power Pivot) for cross‑chart slicers.

Pivot Tables for Dashboard Creation

A dashboard pulls together multiple visualisations that share a common data source and allows interactive filtering through slicers. The key is to build a master pivot table that contains all the necessary fields (dates, categories, metrics) at the lowest granularity, then create derived pivot charts that each show a different metric. A single slicer can then filter all charts simultaneously.

Building a Master Pivot Table

Start with the raw data table. Insert a new pivot table and add rows or columns to define the granularity – the level at which each metric will be shown. A good granularity for time‑series analysis is Date (or Week Number) combined with SKU Classification (or SKU Name).

Why Max for Sale Price? At the lowest level (one date + one SKU) there is only one price per day. Using Max avoids summing duplicate entries that may appear from the raw data. For aggregated totals (e.g., revenue, tonnage) use Sum.

After adding fields, tidy the pivot table layout:

• Design tab → Subtotals → Off
• Grand Totals → Off
• Report Layout → Show in Tabular Form
• Repeat All Item Labels

This gives a clean, row‑by‑row table where every date and category appears explicitly.

Creating Linked Charts

From the master pivot table, create three separate pivot charts (line charts are typical for trends), each using the same master table as its data source. The row fields (Date, Week, SKU Classification) stay the same; only the value field changes.

To create a chart: select the master pivot table → Insert → Pivot Chart → Line. In the PivotChart Fields pane, remove the previous value field and add the desired one.

Adding Interactivity with a Slicer

Add one slicer (e.g., on SKU Classification) and connect it to all pivot tables that feed the charts.

1. Click on any pivot chart or table → Insert → Slicer → choose SKU Classification.
2. Right‑click the slicer → Report Connections and tick every pivot table (master table plus the three chart‑specific ones).
3. The slicer now filters all charts simultaneously.

Arrange the three charts side‑by‑side on a dedicated dashboard sheet. The slicer sits next to them for easy filtering.

Example Dashboard Analysis

After filtering for Fruits, a manager observes:

• Sale Price decreased week on week
• Tonnage increased steadily then sharply dropped in week 9
• Revenue rose initially, then crashed when tonnage fell

Such side‑by‑side comparison reveals cause‑effect relationships (e.g., price cuts may have driven volume until a disruption occurred). The same logic applies to other categories like Onion, Potato, Tomato.

Exam tip: The master pivot table approach avoids duplicating slicers and ensures all charts stay in sync. Remember to set numeric fields to the correct aggregation (Max for unique values, Sum for totals) at the chosen granularity.

Key takeaways

• Build one master pivot table with all needed dimensions (date, week, SKU) and metrics (price, revenue, tonnage).
• Use Max for fields that have a single value per row (e.g., sale price) and Sum for aggregated totals.
• Create separate pivot charts for each metric, all sourced from the same master table.
• Add a single slicer and link it to every pivot table in the dashboard.
• Side‑by‑side charts enable instant comparison of trends and help identify relationships between price, volume, and revenue.

Dashboard Creation with Pivot Tables and Slicers

A dashboard consolidates multiple visualizations linked by a common filter. Pivot tables feed pivot charts, and slicers enable interactive filtering across all charts derived from the same base table.

Constructing a Master Pivot Table

To manage multiple views efficiently, create one master pivot table containing all necessary fields aggregated at the desired granularity (e.g., week level). From this master, generate separate smaller pivot tables for individual charts.

Steps:

1. Insert a pivot table from the raw data, adding it to the data model for advanced calculations.
2. Place fields: row labels (e.g., week number), values (e.g., max of total orders, max of percentage veggies, max of percentage fruits).
3. Set value field settings to appropriate aggregation – max for daily totals (to avoid double-counting when aggregating by week), average for percentages.
4. Remove subtotals and grand totals, apply a tabular layout for clarity.

Example fields in master pivot:

Creating Linked Charts from Sub-Pivots

Insert new pivot tables using the master pivot’s range as source. This ensures all sub-pivots share the same underlying data.

• Sub-pivot 1: Week number → Max(Total Orders) – line chart for order volume trend.
• Sub-pivot 2: Week number → Avg(% Veggies), Avg(% Fruits), Avg(% Both) – line chart for order mix distribution.

Exam tip: When using percentages, aggregate by average per week, not sum. Summing percentages across weeks is meaningless.

Adding Slicers and Linking Across Charts

Insert a slicer on a common field (e.g., week number). To link it to multiple pivot charts:

• Right-click the slicer → Report Connections – select all charts that should respond.
• All pivots must originate from the same base table (or the same master pivot) for the slicer to filter them simultaneously.

Analyzing Price–Volume–Revenue Interactions

By combining slicers with pivot tables showing price, tonnage (quantity), and revenue, a manager can spot anomalies. Key observations for SKUs such as Tomato, Potato, Onion, Fruits, and Essentials:

Lesson: Revenue $= \text{Price} \times \text{Quantity}$. A price drop can offset volume gains, leading to flat or falling revenue. Slicers allow zooming into specific time windows to isolate such dynamics.

Key Takeaways

• Build a master pivot at the desired aggregation level (e.g., week) to serve as a single source for multiple charts.
• Use average for percentage fields when aggregating over time; use max to avoid summing daily totals.
• Slicers filter all connected pivot charts simultaneously only if those pivots share the same base data range.
• Price–volume–revenue analysis reveals whether revenue follows tonnage or gets eroded by price declines.
• Dashboard interactivity enables managers to drill into specific periods (e.g., 4 weeks) and compare trends across SKUs or categories.

Pivot Tables – VII: Customer & Regional Segmentation

Customer-level data (sales, customer, SKU) with 50,000 rows can reveal powerful insights when summarized with PivotTables and simple formulas. The goal is to segment customers and regions without charts, using only PivotTable summaries and IFS logic.

---

Extracting Customer Metrics

Create a PivotTable with Customer ID in rows and the following measures (all as summed values unless noted):

These raw measures are then used to compute derived metrics:

• Return % = Sum (Return QTY) / Sum (Build QTY)
• Average Order Value (Ticket Size) = Sum (Build Value) / Count of Delivery Date

Exam tip: Adding a measure directly inside the PivotTable (e.g., using Value Field Settings > Show Values As or a calculated field) keeps the analysis dynamic. A separate column with =IFS(...) formulas can reference the PivotTable output.

---

Customer Segmentation with Formulas

Once the PivotTable is built, segment customers by adding helper columns with IFS (or nested IF) formulas.

Value Category (based on Total Build Value)

=IFS( [@[Sum of Build Value]] > 200000, "Category 1",
      [@[Sum of Build Value]] > 100000, "Category 2",
      [@[Sum of Build Value]] > 50000, "Category 3",
      TRUE, "Category 4")

Handle blanks by converting to zero (e.g., with IF or IFERROR).

Return Nature (based on Return %)

Frequency (based on Distinct Order Count per month)

Exam tip: The same logic can be extended to Average Order Value — high ticket size is desirable even if order count is low.

---

Regional Analysis (Same PivotTable, Different Row Field)

Replace Customer ID with Locality Cluster to see aggregated regional performance.

• Sorting by Build Value (descending) immediately reveals top regions.
• Example insight: JP Nagar and Nagarbhavi (south-west Bangalore) are most valuable; Yeswanthpur underperforms, possibly due to competition (APMCR).

---

Key Takeaways

• PivotTables alone (without charts) can yield actionable insights for managers — summary in 8 rows instead of 50,000.
• Derived metrics (return %, average order value) are critical for segmentation.
• Formulas (IFS) convert raw PivotTable outputs into meaningful customer tiers (value, return nature, frequency).
• Regional analysis follows the same pattern: replace the row field and apply the same measures and sorting.
• Segments drive action – high value + high frequency customers get offers; high return customers need investigation.
• Always handle empty cells in PivotTable outputs (e.g., force 0 for blank build value).

Data Insights and Dashboard Creation

Module 2 transformed raw transaction logs into a live decision tool – a manager's cockpit that consolidates key metrics into a single interactive view. Instead of static spreadsheets, you now have a system that answers: Who are my most valuable customers? Which area returns the most? How do revenues shift with price and volume?

A manager's cockpit is a dashboard that consolidates key metrics into one interactive view, allowing on-demand drill-down and slicing.

What We Built

• Pivot Tables – Clean, aggregated summaries from raw data (daily sales trends, SKU-level order patterns, geographic breakdowns).
• Dynamic Pivot Charts – Visual representations of business movements (e.g., revenue trends, volume shifts) that update automatically when underlying data changes.
• Interactive Dashboards – A collection of pivot charts bound together with slicers and filters so users can slice data instantly (e.g., filter by region, time period, product category).
• Customer Segmentation – Logical rules and thresholds applied to segment customers (e.g., by geography or value). Segments are defined using decision logic – for example, high-value customers meet threshold conditions on revenue, frequency, or recency.

Components of the Dashboard

Process: Raw Data to Dashboard

Insights Unlocked

• Most valuable customers – Who contributes the most revenue or frequency? Segments are revealed by logical thresholds.
• Best-performing areas – Which geography brings the highest returns? Geographic segmentation isolates top regions.
• Revenue vs. price and volume – How do revenues change when price moves or volume shifts? The dashboard surfaces trends and correlations.
• Behavioral interpretation – Beyond numbers: identifying gaps, surfacing growth opportunities, and understanding why patterns occur.

Exam tip: Segmentation by geography and logic is covered here. Behavioral segmentation (RFM – recency, frequency, monetary) appears in Module 3. Do not confuse the two; RFM uses weighted scores, not simple thresholds.

Key Takeaways

• Module 2 built a manager's cockpit from raw data using pivot tables, charts, slicers, and filters.
• Customer segmentation relies on logical rules and thresholds (e.g., geography, value thresholds).
• Dynamic dashboards answer: most valuable customers, best regions, revenue–volume–price relationships.
• The focus is on structured business dashboards – not advanced analytics (that comes in Module 3 with CLV, cohort analysis, RFM, and clustering).