HCLTech • Data Analyst Internship • Riyadh, Saudi Arabia • Aug-Sep 26

Building a Power Bi Dashboard

Enterprise Multi-Page Power BI Architecture, Custom DAX Modeling & Resource Optimization for 1,470 Employees

1,470

Total Employees

28.3%

Utilization Rate

$119M

Total Sales

$16.89M

Gross Margin

3 of 9

At-Risk Projects

16.12%

Overall Attrition

01 //

Page 1: Executive Dashboard

High-level operational overview synthesizing macro revenue pipelines, project portfolio health indicators, monthly gross margin velocity, and client delivery distribution across global regions.

Click to Enlarge (4K Fullscreen)

Project Portfolio Health 33.3% Risk Ratio

Identified 3 at-risk projects out of 9 total active initiatives (6 Good [66.7%], 3 At Risk [33.3%]) through automated status classification. Allowed project management leadership to proactively intervene before delivery deadlines or gross margins slipped.

Commercial Revenue & Margin Velocity $119M Sales Pipeline

Visualized monthly revenue trends reaching $20M+ peaks in Q4 alongside $16.89M in gross margin. Tracked revenue contribution across key enterprise clients (Delta Co, Alpha Co, Zeta Global, Beta LLC, Gamma Inc, Epsilon Ltd) and segment allocations (Government, Small Business, Enterprise).

Geographic Analysis

Global Revenue Mapping

Interactive choropleth map correlating sales volumes with regional operations across North America, Europe, Africa, Australia, and Asia-Pacific to evaluate territorial market penetration.

Client Attribution

Account Revenue Ranking

Pareto distribution of client billings identifying top enterprise accounts contributing over $0.2M+ in recurring billings versus midmarket tier clients.

Market Segments

Segment Treemap Breakdown

Hierarchical treemap visualizing market share across Government contracts, Small Business engagements, Enterprise clients, and Channel Partners.

02 //

Page 2: Workforce & Resource Utilization

Deep-dive operational analytics dashboard modeling headcount allocation across 1,470 personnel, billable vs. bench ratios, departmental compensation structures, overtime intensity, and attrition vulnerability.

HCLTech Workforce & Resource Utilization Power BI Dashboard

Click to Enlarge (4K Fullscreen)

Workforce Utilization Modeling 28.3% Utilization Rate

Tracked active billable engagement versus bench overhead across core technical job roles (Research Scientist, Sales Executive, Laboratory Technician, Manufacturing Director), delivering actionable visibility into staff deployment efficiencies.

Departmental Attrition Diagnostics Comparative Benchmarks

Diagnosed flight-risk concentrations showing Sales leading departmental attrition at 20.63%, Human Resources at 19.05%, and Research & Development exhibiting the strongest retention stability at 13.84%.

Headcount Demographics

1,470 Employee Census

Full headcount mapping covering 882 Male personnel (60%) and 588 Female personnel (40%), enabling cross-filtering across seniority levels and technical departments.

Operational Workload

Overtime Intensity (28.3%)

Pinpointed that 28.3% of the workforce actively logged overtime hours, correlating workload exhaustion with the corresponding departmental attrition spikes in Sales.

Financial Allocation

$10M Payroll Allocation

Multilevel treemap dissecting compensation expenditure across Research & Development, Sales, and HR to quantify average cost and revenue yield per employee ($697).

03 //

Data Architecture & DAX Measures

Behind the visual layer, the analytics engine relied on an automated Power Query ETL pipeline, a star schema relational data model, and custom DAX measures engineered to calculate dynamic utilization and risk thresholds on the fly.

Sample Custom DAX Formulations

// 1. Dynamic Workforce Utilization Rate Utilization % = DIVIDE( CALCULATE( COUNTROWS('Employees'), 'Employees'[BillableStatus] = "Billable" ), COUNTROWS('Employees'), 0 ) // 2. Dynamic Departmental Attrition Rate Attrition % = DIVIDE( CALCULATE( COUNTROWS('Employees'), 'Employees'[Attrition] = "Yes" ), COUNTROWS('Employees'), 0 ) // 3. Portfolio Project Risk Classification Projects At Risk = CALCULATE( DISTINCTCOUNT('Projects'[ProjectID]), 'Projects'[HealthStatus] = "At Risk" )

Power Query ETL & Star Schema

Data Ingestion & Cleansing: Imported messy employee tracking logs and transactional billing tables. Cleansed null values, standardized date formatting, and created unified employee surrogate keys using Power Query.
Star Schema Optimization: Structured a central fact table (Fact_WorkforceUtilization) linked through one-to-many relationships to five dimension tables (Dim_Employee, Dim_Project, Dim_Client, Dim_Department, and Dim_Date).
Interactive Slicer Synchronization: Configured cross-filtering across 6 global slicers (Year, Department, Country, JobRole, Project Name, Client Name) with bidirectional cross-filtering disabled to preserve sub-second query performance.

Official Industrial Certification

HCLTech Internship Certificate

Verified certificate of completion for the Data Analytics Internship at HCLTech, certifying expertise in enterprise business intelligence, dashboard modeling, and operational performance analysis.

View HCLTech Certificate (PDF)