I think that the fact that Nietzsche's philosophy is still taught and widely discussed today precludes it from being a complete failure.
I also believe Nietzsche's primary aim/project was the revaluation of values. Though admittedly by Nietzsche's lights that should result in more overmen. The great irony that runs through Nietzsche's work is that he is simultaneously rejecting systems of value whilst putting forward his own. I don't think this irony would have been lost on him, and interpret Nietzsche as offering up his table of values in an effort to urge those concerned with human flourishing to do the same.
There are certainly figures from history that N has written about admirably, Caeser, Goethe, Ceasare Borgia and of course himself. The concept of the uber-mensch has a tendency to be over emphasised in N's work, with N himself devoting relatively little work to the topic. That said, there are certainly traits he admired, and an overview of these can be found in Brian Leiter's Nietzsche on Morality (particularly in chapter 4). Based on the criteria Leiter identifies (solitariness, responsibility seeking, resilience, life affirming and self reverence) I'm not sure we can definitively say that no such people exist today, figures like Elon Musk spring to mind.
A related concept is discussed extensively in Zarathustra, that of the 'last men'. A sort of banal, unimpressive, stilted person that he sees as the inevitable consequence of societies' tendencies to regard happiness as the ultimate aim. This seems to have a degree of relevance when one considers (at least Western) society today.
Was his mistake to think that mankind can be a going under, or in his specific targeting of morality?
I'm not sure what you mean by the first part, but his targeting of morality was indispensable to his revaluation project for obvious reasons.