Problem
Commission and contract files in this workflow arrived with the usual operations mess: missing rates, duplicate contracts, mixed date formats, and totals that did not match. None of that is a visualization problem until the data is honest.
- Missing commission percentages
- Duplicate contract records
- Inconsistent date formats
- Mismatch between contract amount and commissions
Approach
- Import and inspect both datasets
- Clean missing values, standardize formats, remove duplicates
- Recalculate commissions and cross-check contract totals
- Calculate regional KPIs and monthly trends
- Flag records more than 15% from average commission
- Build the Power BI reporting layer
Stack
- Excel — formulas, inspection, pivot tables
- Power BI — KPI cards, regional and trend views
- Python / pandas — optional wrangling
- Google Sheets — collaborative review
Data
commission_data.csv: agent_id, contract_id, date, amount, commission_pct, region, commission
contract_data.csv: contract_id, start_date, end_date, client_name, total_value
Reporting
- Bar chart: total commission by region
- Line chart: monthly commission trends
- Table: contract validation summary
- KPI cards: total contracts and average commission percentage