Claims Platform Analytics Dashboard
The Problem
The Commercial Claims department tracked claims platform metrics in a SharePoint list but had no analysis or historical preservation process. When the list reached capacity, leadership would delete all data and start fresh. This approach destroyed historical records with every reset cycle, eliminating any possibility of trend analysis or identifying quality issues.
Commercial Claims supervisors recognized the need for analytics capabilities and requested dashboard support from workforce management leadership. The project was delegated as a Power BI skill-building opportunity. Claims team members logged platform interactions in the tracking system, but supervisors lacked visibility into documentation quality. A critical gap existed where team members would mark interactions with specific reason codes but leave required detail fields blank. Supervisors were unaware this documentation problem existed and had no method to identify coaching opportunities, track resolution patterns, or analyze performance trends.
The Solution
The solution required two phases. Phase one addressed data preservation through a Power Automate scheduled workflow that copied the SharePoint list to an Excel archive, then cleared the list to prevent capacity issues. This automation preserved historical records for the first time and ran continuously to build a comprehensive dataset.
Phase two integrated the live SharePoint data with archived Excel data using Table.Combine in Power Query to create a unified data model. M code transformations standardized resolution fields, replaced null values with "Issue Left Blank" flags for tracking incomplete documentation, applied default values for blank skill assignments, and removed unnecessary SharePoint system columns.
The key innovation was a DAX measure that identified coaching opportunities by detecting interactions marked with specific reasons but missing required detail documentation. Field parameters enabled dynamic pivoting between analytical dimensions without rebuilding visuals. The dashboard included a performance overview with metrics cards and trend charts, plus a coaching opportunities analysis with team member rankings showing documentation quality gaps.
The coaching opportunities view provided sortable tables showing which team members had quality gaps, with exact counts and percentage contributions. Visual ranking highlighted top coaching priorities. Analysis revealed patterns in blank documentation, including correlations with specific skill groups and call duration clusters.
Architecture
Two-phase architecture combining Power Automate data preservation with Power BI analytics. Phase one uses scheduled Power Automate workflow with SharePoint list monitoring, Excel file write action, and list clearing action to preserve historical data. Phase two implements Power BI dashboard with multi-source data model using Table.Combine to merge live SharePoint tables and archived Excel data. Power Query M code handles record expansion, null replacement, resolution field standardization, and column cleanup. DAX measures implement coaching opportunities logic with CALCULATE, COUNTROWS, ISBLANK, and OR conditions. Field parameters enable dynamic analytical pivoting across dimensions without separate report pages.
Key Implementation Decisions
- •Two-phase approach prioritized data preservation before analytics, ensuring historical dataset existed for analysis
- •Power Automate archive automation over manual exports, eliminated human error and ensured continuous data preservation
- •Table.Combine strategy for multi-source integration, merged live and archived data into unified analytical model
- •Null replacement with "Issue Left Blank" flag transformed missing data into actionable coaching metric
- •Field parameters over separate report pages, enabled dynamic analytical pivoting with single slicer control
- •Coaching opportunities DAX logic quantified previously invisible quality gap with team member ranking
- •Scheduled workflow execution ensured zero maintenance burden after initial deployment
The Results
Quantifiable Outcomes
- ✓Commercial Claims supervisors adopted the dashboard for historical trend analysis and coaching insights
- ✓Revealed previously invisible documentation quality gaps across the claims team
- ✓Provided specific metrics for targeted coaching conversations rather than general feedback
- ✓Established first-ever historical data preservation, capturing months of trend data
- ✓Power Automate automation ran continuously with zero maintenance requirements
- ✓Demonstrated advanced Power BI capabilities including field parameters, complex DAX logic, and multi-source data integration
- ✓Recognition received from requesting Commercial Claims supervisors and direct workforce management supervisor
Lessons Learned
- →Proactive data preservation prevents catastrophic loss: Power Automate archive automation saved months of historical data before it could be deleted
- →Quality gaps invisible without metrics: Supervisors completely unaware documentation problem existed until dashboard quantified the issue
- →Field parameters enhance dashboard flexibility: Single slicer enabled pivot between analytical dimensions without separate report pages
- →Multi-source integration expands analytical capability: Combining live SharePoint with archived Excel created historical analysis impossible with single source
- →Coaching opportunities require quantification: Ranking team members by specific quality gap with percentage contributions enabled targeted interventions
- →Two-phase approach prioritizes foundation: Building data preservation before analytics ensured historical dataset existed for analysis