Explore how interactive filtering by year, month, agent, category, and priority dynamically updates customer support KPIs and operational metrics across the dashboard.
The dashboard follows a star schema designed to optimize filtering, improve model performance, and simplify DAX calculations.
The solution is built using two fact tables and four dimension tables, following Power BI dimensional modeling best practices.
- Fact_Cases – Stores customer support case information, including resolution time, SLA compliance, priority, category, and reopen status.
- Fact_Agent_Performance – Stores monthly agent performance metrics such as QA score, cases solved per day, SLA performance, and attendance.
- Calendar – Date dimension used for time intelligence and report filtering.
- Dim_Agent – Agent information.
- Dim_Category – Customer support case categories.
- Dim_Priority – Priority levels for customer support cases.
This star schema improves report performance, simplifies relationships, and enables efficient cross-filtering across all dashboard pages.
Several custom DAX measures were developed to calculate business KPIs and evaluate customer support performance.
Weighted Overall Performance =
[QA Attainment] * CALCULATE(
SELECTEDVALUE(KPI_Targets[Weight]),
KPI_Targets[KPI] = "QA"
)
+
[Solved Attainment] * CALCULATE(
SELECTEDVALUE(KPI_Targets[Weight]),
KPI_Targets[KPI] = "Solved per Day"
)
+
[Reopen Attainment] * CALCULATE(
SELECTEDVALUE(KPI_Targets[Weight]),
KPI_Targets[KPI] = "Reopen Rate"
)
+
[SLA Attainment] * CALCULATE(
SELECTEDVALUE(KPI_Targets[Weight]),
KPI_Targets[KPI] = "SLA"
)
Calculates an overall weighted performance score using KPI targets and configurable business weights.
SLA Met % =
DIVIDE(
CALCULATE(
COUNTROWS(Fact_Cases),
Fact_Cases[SLA_Met] = "Yes"
),
COUNTROWS(Fact_Cases)
)
Measures the percentage of customer support cases resolved within the defined Service Level Agreement (SLA).
Reopen Rate % =
DIVIDE(
CALCULATE(
COUNTROWS(Fact_Cases),
Fact_Cases[Reopened] = "Yes"
),
COUNTROWS(Fact_Cases)
)
Calculates the percentage of support cases reopened after being marked as resolved.
Median Resolution Days =
MEDIAN(Fact_Cases[Resolution_Days])
Returns the median number of days required to resolve customer support cases, reducing the influence of outliers.
QA Target =
CALCULATE(
SELECTEDVALUE(KPI_Targets[Target]),
KPI_Targets[KPI] = "QA"
)
Retrieves KPI targets dynamically from the KPI_Targets table, allowing target values to be maintained without modifying DAX code.
Build an interactive Power BI dashboard to monitor customer support operations by analyzing agent performance, operational efficiency, service quality, and case trends.
- How many customer support cases are handled over time?
- Which agents consistently achieve the highest performance?
- Are QA, SLA, and productivity targets being met?
- Which support categories generate the highest workload?
- Which case categories require the longest resolution time?
- Which categories have the highest reopen rates?
- How does SLA compliance vary across different support categories?
- How does case volume evolve throughout the year?
- Interactive filtering by year, month, agent, category, and priority
- KPI cards
- Weighted KPI attainment
- Dynamic target lines
- Multi-page dashboard
KPI Description
Overall Performance Weighted KPI score QA Score Median quality assurance score Cases Solved per Day Median daily resolved cases SLA Compliance Percentage of cases meeting SLA Resolution Days Median resolution days Reopen Rate Percentage of reopened cases
-
Technical Issues represented 35% of all customer support cases, making them the most common request type.
-
Overall SLA Compliance averaged 96%, exceeding the departmental target of 95%.
-
The median resolution time remained at 3 days, with Complaint cases taking the longest to resolve.
-
Reopen Rate averaged 8%, remaining below the target threshold of 10%.
-
The team achieved an average Weighted Overall Performance of 108%, exceeding business targets across the evaluated KPIs.
-
Customer support volume remained stable throughout the reporting period, averaging approximately 800 cases per month.
- Power BI
- Power Query
- DAX
- Microsoft Excel
- Star Schema Data Modeling
- Dashboard Design
- Business Intelligence
- KPI Development
- DAX
- Data Visualization
- Interactive Reporting
High-level KPIs and trends.
Individual performance and KPI attainment.
Case distribution, SLA, resolution time, reopen rate and monthly trends.
Synthetic customer support dataset created for portfolio purposes.
Transforms operational support data into actionable business insights.
- Forecasting
- CSAT
- First Response Time
- Drill-through pages
- Live Zendesk integration
Daniel Betancourt
Electronic Engineer | Data Analyst
If you found this project interesting, feel free to connect with me on LinkedIn or explore my other data analytics projects on GitHub.