Use FORMAT if you want to have a specified significant number of decimal values in MB. N3 here is for showing 3 decimal numbers.

(CASE WHEN D.DocumentSize < 1000000 THEN
           CONCAT(CEILING(D.DocumentSize / 1024.0), 'KB')
      ELSE 
           CONCAT(FORMAT(D.DocumentSize / 1048576.0, 'N3'), 'MB')
 END) AS DocumentSizeText

How do you want to round the values? If you want to round up, use ceiling() :

(CASE WHEN D.DocumentSize < 1000000 THEN
           CONCAT(CEILING(D.DocumentSize / 1024.0), 'KB')
      ELSE CONCAT(CEILING(D.DocumentSize / 1048576.0), 'MB')
 END) AS DocumentSizeText,

In the original question, it suggested the values should be rounded up when it said
346 bytes is going to display 0KB also, so ideally displaying 1KB would be ideal as a minimum

However clarification comments against an answer suggest that decimal values should be shown, rather than rounding:

It displays 2MB though for 1458250 when it should be 1.45MB

Further, the question said

I'm needing to display a result set ... in KB if the document is less than 1MB or in MB if the document is larger than 1MB

However the suggested answer posed in the question looked at splitting the data on 1000000 bytes, which is really only an approximation of 1 megabyte and all prior answers followed that lead. Funnily enough, when formatting the data, the exact value of a megabyte is used: 1048576.

I'll provide three answers below -

• one that rounds up, as implied in the question
• one that provides decimal values, as implied in the comments
• one that rounds properly (up or down)

I'll extend the answer to show Gigabytes too (even though the question doesn't ask for it - just remove that clause if it is not required, but I think readers may find this extended definition helpful). In both cases I'll use the accurate definitions of megabyte and gigabyte. Full credit goes to the earlier answers which actually solved the initial problem.

Rounding up to nearest value

If dealing with file size, this is being conservative and counting more bytes than is actually being reported.

case 
    when D.DocumentSize < 1048576 then concat(ceiling(D.DocumentSize / 1024.0), ' KB')
    when D.DocumentSize < 1073741824 then concat(ceiling(D.DocumentSize / 1048576.0), ' MB')
    else concat(ceiling(D.DocumentSize / 1073741824.0), ' GB')
end as [DocumentSizeText Rounded Up]

Showing 3 decimal values

Perhaps more accurate, and less readable.

case 
    when D.DocumentSize < 1048576 then concat(format(D.DocumentSize / 1024.0, 'N3'), ' KB')
    when D.DocumentSize < 1073741824 then concat(format(D.DocumentSize / 1048576.0, 'N3'), ' MB')
    else concat(format(D.DocumentSize / 1073741824.0, 'N3'), ' GB')
end as [DocumentSizeText With Decimals]

Rounding properly (up or down)

More accurate than rounding up ... when summing multiple values.

case
    when D.DocumentSize < 1048576 then concat(format(D.DocumentSize / 1024.0, 'N0'), ' KB')
    when D.DocumentSize < 1073741824 then concat(format(D.DocumentSize / 1048576.0, 'N0'), ' MB')
    else concat(format(D.DocumentSize / 1073741824.0, 'N0'), ' GB')
end as [DocumentSizeText Rounded Proper]

TRIM((SELECT TOP (1) 
         STR (@Bytes/(POWER(CAST(1024 AS BIGINT), o-1)*1.0), 8, 2) + SPACE(1) + p
  FROM   ( VALUES (1, 'B'), (2, 'KB'), (3, 'MB'), (4, 'GB'), (5, 'TB') )_u(o, p)
  WHERE  @Bytes < POWER(CAST(1024 AS BIGINT), o)
  ORDER BY o )) [Size],