As Kane said, the CTE is only available in the SQL statement where it is written. Another possible solution, depending on the specifics of your situation, would be to include the COUNT(*) in the single query:
;WITH MyBigProducts AS
(
SELECT
Name,
COUNT(*) OVER () AS total_count
FROM
Products
WHERE
Size="Big"
)
SELECT
Name,
total_count
FROM
MyBigProducts