The thing is, he does not want to be an ascended powerful vampire lord! Multiple dialogue bits here and there show you this. What he wants is to be safe and so that no one can hurt him again. He is convinced the only way to do that is through ascending. If you persuade him to not do that, he later thanks you and confirms that he was close to losing himself completely. Moreover, you can tell the spawn Astarion "don't worry, I will keep you safe" and he will be like "I appreciate the sentiment, it would have been nice to be able to protect myself but oh well". Ultimately he has no regrets about not ascending, more than that, he expresses that he prefers that outcome to the alternative. And if you still want to make him a vampire leader, there is still one way to do that!
See sometimes traumatised people think they want something, but what they need is a completely different story. You can support their rights and wrongs (lol) or you can be the reason they do a little bit of thinking before diving off the edge.
