Let’s talk about power first and then superpowers. The definition of power is the ability to do something or act in a particular way, especially as a faculty or quality. Another meaning can be the capacity or ability to direct or influence the behavior of others or the course of events. There are tons of stories about people of power or that gain power and are very compelling. You don’t need to go to the superhero trend.
Titanic the movie has a lot of power and power shift throughout the story. From money, freedom, influence, and others. Just want you guys to understand that you can write something, put an antagonist force and you don’t need it to be resolved with superpowers. But if you are writing something that involves some supernatural powers, make sure that you have something that does not go overboard. Your character or characters struggle and don’t blow up your worldbuilding into smithereens letting everything hang on a loose and no way on putting things back together.
On the MCU one modification, they modified one character so that the story had a life, a struggle, and could help to make the villain look mightier so that the stakes look higher. Who was that? No other than The Hulk. The Hulk is this Dr. Jeckyll and Mr. Hyde character that was affected by gamma radiation. Every time he fights he gets angrier, stronger, bigger, and can ultimately have solved the villains with brute force without needing so many side characters. So the writers decided of not going full comic book The Hulk and focus on how they could tell a great story divided in all the movies they did, without taking the essence of the character.
So what about superpowers? Just that. If you are going to write about powers, magic, celestial, demonic, occult, anything that would boost any character above your average human, you need to put a balance on things. Making a villain too powerful or a hero extremely powerful would make a story very dull. You need to look for a balance. Stories like Dragon Ball Z, tell you there is always a stronger opponent than before. On Harry Potter armies of wizards were built to counter Voldemort’s minions. You need to look for ways to balance the story so it doesn’t tip to either side unless you know already how to stabilize it so that the obstacles along the way help the losing side even the odds.
Usually, in adventure stories, the hero needs to build up his tool kit, abilities, or gain some trusted ally that will balance out to overcome the central conflict. On Leigh Bardugo’s magical fantasy the Six of Crows she mixes different characters to defeat an institution, thugs, and even an army. What I like about it is that they struggle all the time at any task, even if it is going their way because everything is so well balanced that you don’t know how it is going to turn out until you finish the scene.
So take your character, villain, hero, or secondary. Verify how powerful it is. Is it too powerful for it to be overcome? Would it be balanced throughout the story? Is it becoming everything easy to overtake obstacles? One thing you can do is put everyone against that character. Is it balanced now? If not then take it down a notch. One thing it has been done with Superman is that they play out with his benevolent trait and make him feel bad about his abilities. It is used almost all the time on his backstory when he lived with his adoptive parents the Kents.
Remember you don’t want to sacrifice the potential of your story because you don’t want to modify an all-mighty godlike being that has no match. Otherwise, what is the point of writing the story?