End of the World plots are popular because they are easy. Characters don’t need any personalty or background to save the world. Everyone wants to live and the end of the world is the one situation from which nobody can simply walk away. It’s the absolutely lowest common denominator, there isn’t anything more basic in storytelling than that.
If you want a different kind of threat, then the characters need to have something they want to protect. Bob the chaotic neutral human thief does not value anything but his life. Why should he care and not just run away?
A good idea is to not make the outcome total success or total failure, but to allow for a wide gradient of success. Two popular stories from recent years, in which the heroes fail really badly are Star Wars III, and Halo: Reach. Both are prequels and failure was a given and everyone knew it, but also knew that later on things would still turn out well, so the people in charge of the money were willing to shove over the production money, which they otherwise wouldn’t do.
The Sith almost whipe out the Jedi and take over the galaxy, but Yoda and Obi-wan survive and hide the babies to prepare for a next offensive 20 years later. Reach gets completely annihilated with most of the remaining human forces, but one ship manages to escape with the Master Chief and the coordinates of Halo, which will turn around the whole war, that was already lost.
The heroes failed and spectacularly badly. But even after they lost, they still had to press on, because things could get still much worse. If the PCs of a campaign don’t manage to stop the evil necromancer lord and his army does reach the kingdom and destroys the kings troops, the PCs have failed. But the undead still have not whiped out every town in the kingdom and the PCs can still make a second attempt to kill the necromancer and save whoever is still left. Still great deedstto be done. And if that also fails, they can try to make run for their home town and get everyone to flee the country before the undead arrive. And they can still fail at this again, but there is still the need to get at least some survivors who are trapped in the temple. And maybe some die on the attempt to leave thr village, but the remaining ones still can be saved.
But what is absolutely required for such a campaign is, that the PCs care about anything but their own life and money. Even if the only thing that matters for Bob the Thief is his little brother, who is trapped in the temple with the other survivors, you have something to work with. So get the players to come up with a couple of things they care about. Doesn’t have to be relatives that get taken hostage by the GM. The more things the players can name, the less likely each of them is to be targeted by villains. And it doesn’t even have to get that far. What matters is that at some point in the future, these things could become threatened if the PCs are failing too many times in a row.