Hi all im trying to achieve the below result with my pivot query but it seems not correct.

TO ACHIEVE:

KPIScore    UserID  KPICode July 2024   June 2024
$4.50   XXX000035   FRACRT04    $4.00   $5.00
$0.00   XXX000172   FSACRT01    $0.00   $0.00
$0.00   XXX000172   FSACRT07    $0.00   $0.00

CODE:

TRANSFORM SUM(Score) AS FinalScore
SELECT AVG(Score) AS KPIScore, UserID, KPICode FROM KPI GROUP BY UserID, KPICode, FORMAT(DueDate, 'MMMM yyyy')
PIVOT FORMAT(DueDate, 'MMMM yyyy')

RESULT:

KPIScore    UserID  KPICode July 2024   June 2024
$4.00   XXX000035   FRACRT04    $4.00   
$5.00   XXX000035   FRACRT04                $5.00
$0.00   XXX000172   FSACRT01    $0.00   
$0.00   XXX000172   FSACRT01                $0.00
$0.00   XXX000172   FSACRT07    $0.00   
$0.00   XXX000172   FSACRT07                $0.00