How can I compare values using CTE and Union?

Try this:

with cte as 
(
Select [Index Name], cast([adj count] as int) as 'adj count', count([adj count]) as total
  FROM [Test_DB].[dbo].[SPBMI_Count]
  Where [Index Name] in ('S&P Global Ex-U.S. LargeMidCap (US Dollar)','S&P Emerging SmallCap (US Dollar)',
  'S&P Global BMI (US Dollar)', 'S&P Emerging LargeMidCap (US Dollar)',  
          'S&P Global Ex-U.S. SmallCap (US Dollar)')
          group by [Index Name],[adj count]
)
select *, cte.[adj count] - x.[adj count] from cte 

LEFT OUTER JOIN (
SELECT 'S&P Global BMI (US Dollar)' [index name], count(*) as [adj count]
  FROM [Test_DB].[dbo].[S&P Global BMI (US Dollar) Net Total Return]
  union all
SELECT 'S&P Global Ex-U.S. SmallCap (US Dollar)', count(*)
  FROM [Test_DB].[dbo].[S&P Global Ex-U.S. SmallCap (US Dollar) Net Total Return]
  union all
SELECT 'S&P Global Ex-U.S. LargeMidCap (US Dollar)', count(*)
  FROM [Test_DB].[dbo].[S&P Global Ex-U.S. LargeMidCap (US Dollar) Net Total Return]
  union all
SELECT 'S&P Emerging SmallCap (US Dollar)', count(*)
  FROM [Test_DB].[dbo].[S&P Emerging SmallCap (US Dollar) Net Total Return]
  union all
SELECT 'S&P Emerging LargeMidCap (US Dollar)', count(*)
  FROM [Test_DB].[dbo].[S&P Emerging LargeMidCap (US Dollar) Net Total Return]) x
on cte.[index name] = x.[index name]

where total = 1

order by [adj count] desc

Also, spaces in column names makes babies cry, and puppies eat kittens.