If I'm coming up with an example you better believe it's not going to be a Marvel or DC character.
I suppose the first example that comes to mind is Raistlin from Dragonlance. Literally has the power to overthrow the gods, still manages to get stabbed by a dwarf. And it stays consistent because his limitation is keeping his concentration. If he loses his concentration (such as a random time travelling kender appearing in front of him), he can't cast his magic, and goes from god-slaying archmage to sickly mortal with a funny skin color.