Claims Analytics Ecosystem
The Problem
A Commercial CSA spent hours each month manually processing claims tracking data through a convoluted Excel workflow. Manual process required downloading SharePoint lists, calculating metrics manually, entering data manually from external system for over 100 policy numbers, then formatting everything for supervisor review. Only one supervisor had access to the unwieldy spreadsheet creating knowledge silo and limiting data-driven decision making.
The Excel workbook was unwieldy and widely disliked across the team. Data structure was incompatible with analytics tools, requiring manual transposition before any visualization could occur. Manual calculations led to data errors discovered only after automation began. No interactive analysis capabilities existed. No quality tracking for coaching opportunities. The workflow combined time-intensive manual work with error-prone data entry during repetitive monthly cycles.
The Solution
Evolved solution through three implementation phases transforming manual nightmare into complete analytics ecosystem. Phase one built Power BI dashboard with five-table data model combining live SharePoint data with archived historical records. Developed 70+ DAX measures including month-over-month comparisons with directional arrows, year-to-date calculations, business entity breakdowns, and call routing analysis. Created custom calendar table for time intelligence and dynamic category switching for training quality tracking.
Phase two addressed remaining CSA burden after few days reflection on user experience. Despite eliminating SharePoint downloads and manual calculations, CSA still used Excel for entering data from external system with no API access. Proposed building Power App to write directly to Power BI data source, eliminating Excel entirely. Supervisor immediately approved recognizing value of improved user experience and better visual design.
Phase three developed multi-page Power App handling 100+ policy number inputs organized by geographic state groupings. Six-page navigation with progress indicators, visual completion checkmarks, and seamless Power BI integration through embedded app button. Submit triggers automated Power BI refresh plus delayed email notification to supervisors. Added monthly archive automation moving historical data from active SharePoint list to Excel storage while Power BI seamlessly combines both sources.
Proactively added training quality tracking without request, enabling supervisors to identify coaching opportunities across six categories including missing information and invalid data patterns. Leadership enthusiastically adopted this feature, noting it had never occurred to them that quality tracking at this level of detail was possible. Built version 2 dashboard ahead of planned process changes, ready for instant deployment when leadership approves rollout.
Architecture
Power BI dashboard with five Power Query sources: live SharePoint list (current data), Excel archive (historical data), Table.Combine merge creating unified fact table, reference data for business entities, and Power App write-back list for external system data. Complex claim number validation logic using nested IF conditions checking 10-digit format and special suffix rules. Advanced DAX implementation with 70+ measures including SWITCH-based dynamic category filtering, DATEADD for month-over-month comparisons, CALCULATE with ALL for context-independent totals, custom DATATABLE for training categories with sort order, and formula-driven KPI cards showing variance with directional arrows. Power App uses multi-page form with six state-grouped screens, 4-column grid layout, progress indicators, SharePoint write-back integration, and conditional completion validation. Power Automate workflows handle dataset refresh on submit, 5-minute delayed email notifications with dynamic month names in subject lines, and monthly scheduled archival moving active records to Excel storage with automated list cleanup. First SharePoint list created personally, learned column types and schema design through implementation.
Key Implementation Decisions
- •Three-phase iterative approach over single build: Phase one proved value with dashboard, phase two improved CSA UX after reflection, phase three added ecosystem automation
- •Power App over continued Excel use: Eliminated unwieldy spreadsheet entirely, better UX for monthly data entry, seamless Power BI integration
- •Table.Combine for historical continuity: Active SharePoint list plus Excel archive merged in Power Query enabling unlimited historical analysis without bloated active list
- •Multi-page form with state groupings: 100+ input fields organized geographically across six pages with progress indicators preventing overwhelming single-screen design
- •Proactive training quality tracking: Added six-category coaching opportunity analysis without request, became most valued feature for leadership
- •Delayed email automation: 5-minute wait ensures Power BI refresh completes before supervisor notification with dynamic month names
- •Monthly archive automation: Scheduled workflow keeps active list clean while preserving all historical data for trend analysis
- •Version 2 proactive development: Built process change variant ahead of planned rollout enabling instant deployment when approved
The Results
Quantifiable Outcomes
- ✓Eliminated hours of monthly manual work for CSA across data download, calculations, and entry tasks
- ✓All supervisors gained dashboard access replacing single-person knowledge silo with team-wide visibility
- ✓Training quality tracking revealed coaching opportunities leadership did not know existed
- ✓CSA freed from Excel spreadsheet entirely, now uses visual Power App for monthly data entry
- ✓Data errors from manual calculations eliminated through automated DAX measure computation
- ✓Interactive analysis capabilities enable supervisors to slice by month, business entity, state, call type
- ✓Month-over-month KPI cards with directional arrows and variance percentages show trends at glance
- ✓Six-category quality tracking actively used for coaching conversations and process improvement decisions
- ✓Automated monthly archival maintains clean active list while preserving unlimited historical data
- ✓Power BI refresh and email notifications fully automated, zero manual coordination required
- ✓Leadership using dashboard to inform strategic decisions including process change initiatives
- ✓First Power App built, established foundation for future cross-platform solution development
- ✓Most complex Power BI project completed, demonstrated advanced DAX and data modeling capabilities
- ✓Positive user feedback highlighted ease of use, clarity despite complexity, and actionable insights
Lessons Learned
- →User experience requires ongoing reflection: Phase one success did not mean CSA experience was optimal, returned few days later with Power App idea
- →Proactive features can become most valued: Training quality tracking was unrequested addition, became leadership favorite with enthusiastic adoption
- →Multi-platform solutions require patience: Power App and Power BI integration presented stability challenges with connection breaks requiring troubleshooting persistence
- →SharePoint knowledge transfers across projects: Learning list design for this project enabled diagnosing different team's Forms integration issue
- →Iterative scope expansion builds trust: Asked supervisor for approval to add Power App beyond original scope, immediate support enabled phase three
- →Strategic thinking delivers value: Building version 2 ahead of leadership decision enables instant deployment when change requested
- →Complex solutions can still be accessible: Non-technical users praised dashboard clarity despite 70+ measures and multiple data sources
- →First projects in new tools are learning opportunities: First Power App and SharePoint list provided foundation for future cross-platform work