A Self-Service Performance Report for Salford & Co

Every quarter HR was asked the same questions: how is performance trending, which teams need attention, is training paying off? Each answer meant a new spreadsheet cut. The aim was one report that answers them on demand and refreshes with new appraisal data.

Appraisal records for 753 employees across 2024, holding performance score, goals met, engagement score, tenure, training hours, department, job role, gender, marital status and a manager's remark.

• HR Performance AppraisalThe fact table: one row per appraisal.
• Calendar DimA date table related to the appraisal date. Time intelligence functions such as PREVIOUSQUARTER depend on it.
• Key MeasuresAn empty table used as a folder, so all measures sit in one place.
• QuarterOverQuarter MeasuresA second folder for the comparison and colour logic.

Headline measures

Total Employees = DISTINCTCOUNT('HR Performance Appraisal'[Employee ID])

Avg Performance Score = AVERAGE('HR Performance Appraisal'[Performance Score])
Avg Engagement Score  = AVERAGE('HR Performance Appraisal'[Engagement Score])
Avg Tenure            = AVERAGE('HR Performance Appraisal'[Tenure (Years)])
Avg Goals Met         = AVERAGE('HR Performance Appraisal'[Goals Met])
Avg Training Hours    = AVERAGE('HR Performance Appraisal'[Training Hours Completed])
Total Training Hours  = SUM('HR Performance Appraisal'[Training Hours Completed])

Time intelligence

Previous Quarter Performance =
CALCULATE(
    AVERAGE('HR Performance Appraisal'[Performance Score]),
    PREVIOUSQUARTER('Calendar Dim'[Date])
)

QuarterOverQuarter Performance Change(%) =
VAR LatestDay = LASTNONBLANK('Calendar Dim'[Date], [Avg Performance Score])
VAR CurrentQ  = CALCULATE([Avg Performance Score], DATESQTD(LatestDay))
VAR PreviousQ = CALCULATE([Avg Performance Score], PREVIOUSQUARTER(LatestDay))
VAR PctChange = DIVIDE(CurrentQ - PreviousQ, PreviousQ)
VAR Icon = SWITCH(TRUE(), PctChange > 0, "▲ ", PctChange < 0, "▼ ", "↔ ")
RETURN
    IF(ISBLANK(PctChange), BLANK(), Icon & FORMAT(PctChange, "0.0%"))

Presentation logic

Performance Color Code =
VAR PerformanceDifference = [Avg Performance Score] - [Previous Quarter Performance]
RETURN
SWITCH(TRUE(),
    PerformanceDifference > 0, "#6AA84F",   -- green: improving
    PerformanceDifference < 0, "#E06666",   -- red: declining
    "#F4F3F3")                              -- grey: no change

Dynamic Overview Title =
VAR SelectedEmployee   = SELECTEDVALUE('HR Performance Appraisal'[Employee Name])
VAR SelectedDepartment = SELECTEDVALUE('HR Performance Appraisal'[Department])
RETURN
IF(NOT ISBLANK(SelectedEmployee), SelectedEmployee & "'s Performance Record",
IF(NOT ISBLANK(SelectedDepartment), "Performance Overview for " & SelectedDepartment,
"Overview Of Employee Performance"))

This report has no what-if slider. Its interactive controls are three slicers: quarter, department and employee. They filter every visual and the dynamic title, so a manager can move from company to department to one person without a new report being built.

• QuarterCompares any quarter with the trend line and the quarter-on-quarter card.
• DepartmentIsolates one team, and the title updates to name it.
• EmployeeShows one person's record for appraisal conversations.

Performance bands look almost identical in HR, Marketing, Operations and Sales, so the differences that matter sit with individuals, not teams.

• Within each department, the average scores of the above-average, average and exceptional bands differ by one point or less between teams.
• The scatter of training hours against performance shows no visible pattern: employees with 10 hours and 40 hours of training score across the same range.
• Marital status groups are close to even in size and add little to the performance story. That visual could be replaced with goals met by role.

• No statistical test. The training finding is read from the scatter chart. A correlation coefficient would confirm it.
• One year of data. Four quarters are too few to call a trend. The Q2 dip may be seasonal.
• Personal data. The report shows named employees with manager remarks. In live use, row-level security should limit each manager to their own team, and demographic visuals such as marital status should be justified before they are shown.
• Case-study data. Figures describe a fictional company and are not results from an employer.

1. Target development at individuals and managers, using the employee slicer and manager remarks, since department averages are too similar to prioritise one team.
2. Review training content, not volume. More hours are not linked to higher scores, so assess which programmes change performance.
3. Investigate the Q2 dip with managers before assuming it is seasonal, and watch whether it recurs in 2025.
4. Add row-level security before the report is shared beyond HR.
5. Refresh quarterly from the appraisal system so the report replaces the manual pack for good.

Download the Power BI file (Salford_Performance_Appraisal.pbix). It opens in Power BI Desktop with the data model and every measure described here.

1. Load the appraisal data and create a calendar table. Mark it as a date table and relate it to the appraisal date.
2. Create measure folder tables and the measures in section 03.
3. Apply the colour code measure through conditional formatting on the change card.
4. Add the three slicers and bind the page title to the dynamic title measure.