A 330-measure Power BI semantic model transforming 4 months of raw call-center telemetry into a bilingual operational-intelligence platform.
- Client / Domain: Call Center Operations — Salla (multi-project contact center)
- Project Type: Freelance delivery
- Timeline: February 2026 – April 2026
- Tools: Power BI Desktop, Power Query (M), DAX, Excel, Tabular Editor
- Role: Sole Data Analyst / BI Developer
- Delivery: 4-page interactive report (Executive Summary · Operations · Detailed Analysis · Agents) with bilingual (AR/EN) semantic model and 17-folder measure organization
The operation was running three concurrent projects (Alpha, Beta, Gamma) with a team of 48 agents, but had no single operational dashboard capable of answering four recurring questions: Which projects are under-performing their SLA? Which agents need coaching? Is our forecast drifting? Are we trending up or down month over month? The source data was a single wide Excel file mixing transactions, forecasts, and agent attributes at a daily grain. There was no dimensional model, no standardized KPI definitions, and no time intelligence — which meant every recurring analysis was being rebuilt from scratch in Excel each week.
This project delivered a star-schema semantic model plus a comprehensive measure library that closes those gaps and standardizes how the organization talks about call-center performance.
| Source | Format | Grain | Records | Notes |
|---|---|---|---|---|
| Call center daily metrics (single file) | .xlsx |
Date × Agent × Project | 3,829 rows | Contains offered / handled / within-threshold / abandoned / answer-time + forecast |
| Project list | Derived in Power Query | Project | 3 rows | Extracted via surrogate key generation |
| Agent roster | Derived in Power Query | Agent | 48 rows | Extracted via surrogate key generation |
| Calendar | Generated in Power Query (M) | Daily | 121 rows | Full contiguous calendar 2024-01-01 → 2024-04-30 |
Fact table coverage: 268,680 offered calls · 255,264 handled · 13,416 abandoned · 266,127 forecasted.
Profiled the raw Excel file for grain, null patterns, and duplicate combinations. Confirmed that one row represented one Date × Agent × Project combination and that the same (Agent, Project) pair could appear on multiple dates. Identified that ASA and Answer Time were both present but redundantly — Answer Time ÷ Calls Handled reproduces ASA exactly, so the measure layer was designed to recompute ASA dynamically rather than trust the pre-aggregated value.
Adopted Kimball-style star schema. The raw table (Table1, hidden and excluded from refresh) was normalized into one fact (Fact_Call_Metrics) plus three dimensions (Dim_Date, Dim_Agent, Dim_Project) connected via integer surrogate keys. A fourth table (_Measures) was added as a headless host for all 330 measures — this separation keeps DAX logic decoupled from storage and anchors measures at the top of the Fields pane. The KPI list was scoped against four business questions (project comparison · agent ranking · forecast accuracy · MoM trend) which drove 17 display-folder groupings and four report pages.
Power Query built the calendar, generated surrogate keys, and created the bilingual Day Name Arabic / Day Type Arabic calculated columns. The measure library was built in layers — base aggregations (Total Calls ...) first, then core KPIs (Service Level, ASA, Abandonment Rate, Forecast Accuracy), then composite indices (Service Quality Index, Agent Performance Score, Operational Efficiency Score), and finally the presentation layer (200+ Dashboard/* measures driving MoM deltas, target variance, status colors, and narrative tooltips). A 4-tier Performance Tier classifier was added as a calculated column on Dim_Agent so tier could be used as a slicer axis.
Each dashboard card was designed with a dynamic tooltip narrative — four measures (TP SL - Title / Performance / Trend / Actions) generate real-time explanations of the card's color, variance from target, trend, and recommended actions, escalating from routine monitoring (green) through emergency action (red). Bilingual title measures (Dynamic Title / Dynamic Title - Arabic) adapt to filter context. Conditional formatting relies on a unified color palette (#70AD47 / #5B9BD5 / #FFC000 / #C00000) applied consistently across all KPI cards.
Star schema with a single fact table connected to three dimensions via integer surrogate keys. All three relationships are single-direction, single-active, many-to-one. Dim_Date is marked as the Date Table to enable time intelligence across the entire measure library. Full documentation: 03_data_model/relationships.md.
Documented using KPI Chain — Plain → Logic → DAX:
Service Level (SL)
- Plain: Percentage of incoming calls answered within the agreed response-time threshold.
- Logic: Calls answered ≤ threshold ÷ Total offered calls. Industry benchmark: 80/20 rule.
- DAX:
Service Level (SL) =
DIVIDE(
[Total Calls Within Threshold],
[Total Calls Offered],
0
)
Agent Performance Score
- Plain: 0–100 composite score ranking each agent on overall quality.
- Logic: Weighted blend — Service Level (40%) + ASA normalized to 60s (30%) + inverted Abandon rate normalized to 10% (30%).
- DAX:
Agent Performance Score =
VAR SL_Score = [Agent Service Level] * 100
VAR ASA_Score = MAX(0, (1 - DIVIDE([Agent ASA], 60, 0)) * 100)
VAR Abandon_Score = MAX(0, (1 - DIVIDE([Agent Abandon Rate], 0.10, 0)) * 100)
RETURN
(SL_Score * 0.40) + (ASA_Score * 0.30) + (Abandon_Score * 0.30)
Service Quality Index
- Plain: Single 0–100% score summarizing overall service quality.
- Logic: Weighted blend — SL (40%) + ASA normalized (25%) + inverted Abandon (20%) + Forecast Accuracy (15%).
- DAX:
Service Quality Index =
VAR SL_Score = [Service Level (SL)] * 0.40
VAR ASA_Score = MAX(0, 1 - DIVIDE([Avg Speed of Answer (ASA)], 60, 0)) * 0.25
VAR Abandon_Score = (1 - [Abandonment Rate]) * 0.20
VAR Forecast_Score = [Forecast Accuracy] * 0.15
RETURN
SL_Score + ASA_Score + Abandon_Score + Forecast_Score
Full measure documentation (40 core measures in KPI-Chain format):
03_data_model/dax_measures.mdData dictionary for all 330 measures:03_data_model/data_dictionary.md
The report is a four-page navigable experience with a persistent top-right page selector (Executive Summary · Operations · Detailed Analysis · Agent). All pages share a unified dark theme with glass-card styling, gradient KPI backgrounds, and conditional color encoding (green/blue/amber/red) aligned with the 4-tier performance taxonomy.
Four KPI cards (SL 78.68% · ASA 24.35s · Abandon 4.99% · Forecast 99.04%) each carry star ratings, MoM deltas, previous-month reference values, target variance, Performance Score, and Health Score badges. Below them: the full-period Service Level trend with 7-day moving average and 80%/70% reference lines, three project cards (Alpha / Beta / Gamma) showing side-by-side SL / ASA / Abandon / Service Quality Index, and a donut chart of Agent Performance Tier distribution.
Hovering any KPI card opens a "Performance Status Breakdown" tooltip driven by four DAX measures (TP SL - Title / Performance / Trend / Actions). The tooltip explains why the card is the color it is, shows current vs target with variance, classifies the trend, and gives three contextual action items. This transforms a static card into a self-explaining interface — the stakeholder gets the "why" without opening a deck.
Operational-health page built around five top-of-page status indicators (Handle Rate · Staffing · Workload · Operational Efficiency · Queue Efficiency). Five volume tiles carry their full operational context inline (Handle Rate 95.01%, ASA 24.35s, Operational Efficiency Score 89.69%, Staffing Efficiency 104.26%, Workload Balance 1.20 — flagged "Overloaded"). Bottom row shows a Capacity Utilization gauge at 95.92% and the Operational-Performance-vs-Forecast-Accuracy combo chart that confirms the month-over-month climb.
The same page filtered to a single project demonstrates the dynamic status cascade: Handle Rate degrades from "Good" to "Warning" (93.84%), Queue Efficiency drops from "On Track" to "Slow" (0.71), the Capacity gauge shifts amber at 94.83%, and the three MoM trends in the combo chart rescale to the filtered volumes. Every status label, every color, and every narrative tooltip recomputes from the same DAX with no redundant visuals or hidden duplicates.
Project-comparison workbench. Volume Distribution donut (Alpha 53.21% · Beta 32.43% · Gamma 14.35%), a horizontal KPI-Comparison bar chart breaking SL / ASA / Abandon scores per project, and a ranked matrix showing Project Beta earning the "Best Project Flag" at rank 1. Day-of-week Daily Calls Offered bar chart confirms the flat weekday/weekend pattern. Bottom row combines a Daily Service Level Trend (121 data points with moving average) with a Volume-vs-SL dual-axis chart that lets the viewer visually correlate volume spikes against SL dips.
Team-level context panel at top-left (At Risk: 8 · Team Avg ASA: 24.35s · Team Avg SL: 78.68%), then a four-tier Performance Tier donut matching the Executive Summary's distribution (50% Good · 29.17% Acceptable · 16.67% Needs Improvement · 4.17% Excellent). Workload Distribution bar identifies 3 overloaded agents (Tamer, Jana, Shaimaa shown in red/amber). Agent Performance Matrix on the bottom left with rank badges (🥇 #1 Hesham 80.29% SL · 🥈 #2 Ibrahim · 🥉 #3 Karim), and a Project Distribution stacked bar revealing per-agent project mix (most agents are generalists; a handful are 60%+ specialists in a single project).
Two-table drill-down: "Agents Needs Improvement" (all 8 flagged agents with their SL/ASA/Abandon heatmaps and Improvement Priority scores) plus a full ranked "Agents Performance" matrix. Right-hand Behavior Cluster Scatter plot maps every agent on a Quality (SL) × Quantity (Calls/Day) grid, color-coded by cluster — instantly separating high-volume/high-quality performers (green, top-right) from development-needed agents (red, left side).
All figures below are derived directly from the live semantic model and visible on the dashboards above.
-
Operational turnaround quantified. Service Level climbed from 68.4% in January to 88.5% in April, a +20.1 percentage-point improvement visible as the steady climb on the Executive Summary's SL trend line. Abandonment rate dropped from 9.1% to 1.8% (an 80% relative reduction), and ASA fell from 37.5s to 15.3s (-59%). Each successive month improved on every metric, indicating a sustained coordinated effort rather than noise.
-
Project Beta is the operational benchmark — earns the Best Project flag. Project Beta delivered 84.88% SL / 2.22% abandon / 16.5s ASA on 87,145 offered calls, earning the ⭐ Best Project Flag on the Detailed Analysis ranking matrix. Project Alpha — carrying the largest volume at 53.21% of total calls — delivered only 76.10% SL / 6.16% abandon / 28.9s ASA. Project Gamma was the weakest at 74.25% SL / 6.91% abandon / 30.2s ASA. Alpha's volume weight means it drags the overall SL down despite being the middle performer by quality.
-
Workload balance index flags operational stress. The Operations page shows Workload Balance Index at 1.20 — outside the 0.8–1.2 healthy band and flagged "Overloaded." Paired with Staffing Efficiency at 104.26% and Capacity Utilization at 95.92%, the team is running hot: staffing is effectively balanced at the total level, but the work is unevenly distributed — a small number of agents are absorbing disproportionate load.
-
Only 4.17% of agents (2 of 48) reach the Excellent tier. The Agent tier donut confirms: 50% Good (24 agents), 29.17% Acceptable (14), 16.67% Needs Improvement (8), 4.17% Excellent (2). The top-heavy Good tier signals a clear coaching opportunity: moving the 24 Good agents into Excellent requires targeted ASA coaching — most sit within 2–3 seconds of the 23s Excellent cutoff. The top 3 performers are Hesham (80.29% SL, 22.87s ASA), Ibrahim, and Karim.
-
Eight agents are on the At-Risk list and need intervention now. The Agents deep-dive table lists all 8: Nour, Mahmoud, Tamer, Shaimaa, Ashraf, Mohamed, Dina, Ahmed. Their Improvement Priority scores range from 19.2 to 30.2 (higher = more urgent — combines performance gap with call volume carried). Nour tops the priority list at 30.2.
-
Behavior Cluster scatter isolates the coaching segments. The Agent page scatter plot separates the team into 5 behavioural archetypes. The green cluster (top-right) — High Volume/High Quality performers — contains only a handful of agents. The red cluster (left side, sub-78% SL) contains the 8 Needs-Improvement agents, all of whom sit in the 75–88 calls/day productivity band, confirming that the performance gap is quality-driven, not volume-driven.
-
Day-of-week pattern is essentially flat. The Detailed Analysis Daily Calls Offered bar shows range 32K (Saturday) to 43K (Tuesday/Thursday), with SL tightly banded at 78.3%–79.0% across all seven days. This disproves a common industry assumption and means any staffing optimization must target intraday hourly patterns rather than day-level reallocation.
-
Forecast is broadly accurate with mild under-forecast bias. Forecast Accuracy sits at 99.04% (Excellent) with MAPE at 0.96%. Forecast Bias is +2,553 calls — actuals exceeded forecasts, which aligns with the volume growth trend in April. This puts the forecast at the "Under-Forecast" boundary and means staffing plans need a small upward adjustment to keep pace with the April growth rate.
-
Alpha carries 65.7% of all abandoned calls despite representing only 53.2% of offered volume. Every percentage point of abandonment reduction on Alpha would save ~1,430 customer-loss events over a comparable 4-month window. This is the highest-leverage intervention target in the entire operation.
- Single source of KPI truth. 330 measures with standardized definitions eliminate the recurring "whose SL number is right?" disputes across ops / workforce-management / executive reporting.
- Agent coaching is now data-driven. The 4-tier Performance Tier classifier plus Behavior Cluster labels (High Volume/High Quality · Balanced · Quality-Focused · Volume-Focused · Development Needed) provide an actionable coaching taxonomy, replacing ad-hoc judgement with thresholds anyone can verify.
- Bilingual reporting unlocked. Arabic-language day names and day types (عطلة / يوم عمل) plus a dynamic Arabic title measure make the same dashboard consumable by Arabic-first stakeholders without a parallel model.
- Dynamic narrative tooltips. 16 tooltip measures generate real-time explanations of what each KPI card's color means, what drove the change, and what action to take — turning static cards into self-explaining interfaces.
- Four-page navigation covers every stakeholder. Executive Summary for leadership · Operations for workforce-management · Detailed Analysis for business analysts · Agents for team leads — each page optimized for its audience's decision latency without duplicating measures or visuals.
- Extensibility. Adding a new KPI is a matter of declaring a measure in
_Measuresand dropping it in the appropriate display folder — no relationship changes or schema impact.
salla-callcenter-dashboard/
├── README.md
├── LICENSE
├── .gitignore
│
├── 01_raw_data/
│ └── README.md
│
├── 02_cleaned_data/
│ └── README.md
│
├── 03_data_model/
│ ├── erd_diagram.png
│ ├── relationships.md
│ ├── data_dictionary.md
│ └── dax_measures.md
│
├── 04_power_bi/
│ ├── salla-callcenter-dashboard.pbix
│ └── screenshots/
│ ├── 1-executive_summary.png
│ ├── 2-executive_summary_tooltip.png
│ ├── 3-operations.png
│ ├── 4-operations_filtered_project.png
│ ├── 5-detailed_analysis.png
│ ├── 6-agents_page1.png
│ └── 7-agents_page2.png
│
├── 05_documentation/
│ ├── methodology.md
│ ├── kpi_definitions.md
│ └── business_requirements.md
│
└── 06_assets/
└── cover_image.png
Structural note:
03_data_model/contains an additionaldata_dictionary.mdfile (catalogues all 330 measures + column metadata). This is an additive extension to the standard structure — all original files are present, no substitutions.
- Clone or download the repo.
- Open
04_power_bi/salla-callcenter-dashboard.pbixin Power BI Desktop (free from Microsoft Store, Windows only). - Data is embedded — no external refresh required.
- Navigate using the top-right page selector (Executive Summary · Operations · Detailed Analysis · Agent).
- Hover any KPI card for the dynamic tooltip narrative explaining its current status.
- For measure reference while exploring: keep
03_data_model/dax_measures.mdopen alongside the report. - For programmatic inspection: the model supports XMLA read, Tabular Editor 2/3, DAX Studio, and the Power BI Modeling MCP server used to generate this documentation.