Edit: There is actually a cleaner/simpler solution available based on your first approach. See below for the code.
Your second code snippet is already really close to the solution. The counter does not display the heading number since you have not set the heading numbering yet. By default, the heading number is none, see here. The counter will then just return “N” as the dummy value. If you would use #counter(heading).display("1") it would return 0, which is a lot easier to understand than “N” in my opinion.
Since you are creating the heading from scratch, some of the default behavior is lost in your code. If you are interested in the details, please see How can I show the heading number after the heading itself? - #3 by Andrew (this is from the second topic you referenced). You don’t have to replicate the heading completely here, but you should definitely use a Block Function – Typst Documentation. Headings use “sticky” blocks by default to prevent a heading at the end of a page with the content only starting on the next page. See here for an example. Furthermore, with a block you can use the parameter below to adjust the spacing after the heading.
#set heading(numbering: "I")
#show heading.where(level: 1): it => block(
below: 1.5em,
{
set text(size: 14pt, weight: "bold")
set align(center)
upper("TITLE ") + counter(heading).display()
linebreak()
it.body
}
)
= Test 1
#lorem(570)
= Test 2
#lorem(100)
I agree with you that this modification of the heading feels somewhat complicated. After playing around with your first idea a bit, I actually found a simpler solution. The shift of the title you mentioned is caused by the hanging-indent. This is required for a correct formatting of multiline headings. If you set this to zero, the shift will be gone and you can use show-set rules for the other modifications. The required code is then only
#set heading(numbering: (..n) => "TITLE " + numbering("I", ..n) + linebreak(), hanging-indent: 0pt)
#show heading: set align(center)
#show heading: set text(size: 14pt, weight: "bold")
which is a reasonable effort given the level of modification compared to the default heading structure. I hope that you consider this solution easy enough to stick with Typst. 
If you just need to customize the numbering, the package numbly – Typst Universe is really useful. Especially if you need to modify the numbering for different heading levels.