Perhaps the OP simply forgot to include the other cases in the where clause

ChrisM , If I can borrow your code...

Use ProdDB

SELECT TOP (100) PERCENT

[Prod No] = p.ProdNo,

[Prod Name] = p.ProdName,

[% of Total Target] = CASE

WHEN p.Measure = 'P1' THEN m.P1_Pct

WHEN p.Measure = 'P2' THEN m.P2_Pct

WHEN p.Measure = 'P3' THEN m.P3_Pct

WHEN p.Measure = 'P4' THEN m.P4_Pct

END

FROM dbo.prod p

LEFT OUTER JOIN dbo.prod_Master m

ON p.RoleID = m.RoleID

AND p.ProdNo = m.ProdNo

WHERE p.Measure IN ('P1','P2','P3','P4')

AND p.ProdRole NOT IN ('catA', 'catB')

GROUP BY

p.ProdNo,

p.Measure,

p.ProdName,

p.ProdRole,

CASE

WHEN p.Measure = 'P1' THEN m.P1_Pct

WHEN p.Measure = 'P2' THEN m.P2_Pct

WHEN p.Measure = 'P3' THEN m.P3_Pct

WHEN p.Measure = 'P4' THEN m.P4_Pct

END

Is this what you wanted?