Interactive Dashboard: Superstore Case Study

The brief

Case studySuperstore sales review

The Head of Sales wants a one-page Excel dashboard to review performance for 2023–2025. They want to see, at a glance: total sales, profit, profit margin and orders; how sales are trending; which regions, categories and sub-categories perform best and worst; and the effect of discounts on profit. They want to filter by year, region and segment. The dashboard will be emailed every month, so it must be protected against accidental changes.

Step 0: questions and KPIs first

KPI / question	Calculation	Visual
Total sales	Sum of sales	KPI card
Total profit and margin	Sum of profit; profit ÷ sales	KPI cards
Number of orders	Distinct count of order_id	KPI card
Sales trend	Sales by month	Line chart
Regional performance	Sales and profit by region	Bar chart
Product mix	Sales by category and sub-category	Bar chart / treemap
Discount impact	Profit by discount level	Column chart
Filters	Year, Region, Segment	Timeline + slicers

Agreeing on this table before building saves hours of rework. It is also your "requirements document" in interviews.

Here is the finished result we are aiming for (drawn from the real data):

Step 1: prepare the data

1. Import superstore_sales.csv with Data → From Text/CSV (Power Query, Lesson 12), or open it and convert the range to a Table named tblSales (Ctrl + T).
2. Add helper columns that the pivots will need:

Column	Formula (structured reference)	Why
year	=YEAR([@order_date])	Year slicer (or use a Timeline)
month	=TEXT([@order_date],"mmm")	Labels
margin	=[@profit]/[@sales]	Row-level margin for analysis
discount_band	=IFS([@discount]=0,"0%",[@discount]<=0.2,"1–20%",TRUE,">20%")	Simpler grouping
days_to_ship	=[@ship_date]-[@order_date]	Operations KPI

3. Check data quality: blanks (COUNTBLANK), duplicates of row_id, negative quantities, dates outside 2023–2025.

PythonCopy

s.describe(include="all").T[["count", "unique", "min", "max"]].head(12)

Output

	count	unique	min	max
row_id	6,323	NaN	1	6,323
order_id	6323	3200	NaN	NaN
order_date	6323	NaN	2023-01-01	2025-12-31
ship_date	6323	1040	NaN	NaN
ship_mode	6323	4	NaN	NaN
customer_id	6323	785	NaN	NaN
customer_name	6323	424	NaN	NaN
segment	6323	3	NaN	NaN
city	6323	16	NaN	NaN
state	6323	15	NaN	NaN
region	6323	4	NaN	NaN
product_id	6323	78	NaN	NaN

12 rows × 4 columns

Step 2: one pivot table per visual

Create a sheet called Calc (it will be hidden later). Insert one pivot table for each chart, all from tblSales so they share one pivot cache:

Give each pivot a meaningful name (PivotTable Analyze → PivotTable Name: ptTrend, ptRegion …). Names make GETPIVOTDATA formulas and maintenance easier.

Step 3: pivot charts

Click each pivot → PivotTable Analyze → PivotChart → choose the type → cut and paste the chart onto a sheet called Dashboard.

Clean each chart:

• Right-click the field buttons → Hide All Field Buttons on Chart.
• Delete gridlines and legends that are not needed; add data labels where there are few bars.
• Apply the same font and a two-colour palette (one highlight, one neutral).
• Write insight titles, for example "Technology drives most sales" or "Discounts above 20% destroy profit".

Step 4: slicers and a timeline that control everything

1. Select any pivot → PivotTable Analyze → Insert Slicer → tick region and segment.
2. Insert Timeline → order_date → switch the timeline to YEARS.
3. Right-click each slicer → Report Connections → tick all five pivots. Now one click filters every chart and KPI.
4. Format slicers: Slicer tab → Columns = 1 or 2, a matching style, size them to align with the grid.

Step 5: KPI cards and a dynamic title

KPI cards are simply cells or shapes linked to the KPI pivot. GETPIVOTDATA keeps returning the right number even when slicers change the pivot.

• C4=C3/C2
• C6="₹"&TEXT(C2/10^7,"0.00")&" Cr"