In the Vampire Hunter D films, only the top ranking vampires (Magnus, Mier, and Carmilla) come across as suave. Even Lamika is pretty much a Lonely Rich Kid turned Harmless Villain. Rei Ginsei angrily demands Count Lee make him a full vampire; Count Lee just laughs at him.
Puella Magi Madoka Magica: In the backstory, Akemi Homura is the poster girl of this trope in regard to being a Magical Girl. She was a complete loser in the traditional sense before she became a Magical Girl. After becoming a magical girl, she's very bad at fighting Witches. By the time the story proper starts, she's taken enough levels in badass that finding this out is a major shock.
In the graphic novel Life Sucks, our hero Dave is a vampire who refuses to kill people for a blood fix. He gets his blood from the blood bank instead, and his vampire powers are consequently weak.
One of the few vampires in the Marvel Universe to keep his old personality after the transformation was a nerdy producer named Harold H. Harold. He was occasionally a serious problem, but that's because he mostly appeared in Howard the Duck.
The anthology series Beautiful Stories for Ugly Children has an issue called "Arnold: Confessions of a Blood Junkie". Among other things, the title character is still the same overweight nebbish he was when alive (and is in fact too fat to fly in bat form).
Heck; while his powers are great in practice, "strength and speed of a spider" doesn't sound all that impressive when you try to describe it.
Simpsons Comics had a three-part story (spread over three of their four titles) in which the Rigelian aliens Kang and Kodos turn Itchy and Scratchy from cartoon characters into real beings; Itchy and Scratchy then wreak havoc at the Springfield Nuclear Power Plant, causing a core explosion that contaminates the entire town with nuclear energy and turns everyone except Bart into a superhero or supervillain. While some of the nuclear-powered alter egos are genuinely menacing, others just Poke the Poodle (the "Mudslinger" attacks people by splattering mud on them), are not as intimidating as they at first seem (Homer becomes a gluttonous green-skinned moron known as "The Ingestible Bulk"), or are just plain pathetic (Bart's grandfather winds up as an elderly vigilante who talks pretty tough, but keeps falling asleep at the most awkward times).
Jimmy Olsen has the titular character randomly obtain and lose superpowers, and no matter what he does, every superhero still thinks of him as just a regular guy, and not even a third rate sidekick.
Films — Animation
Syndrome from The Incredibles is a terrific villain and a horrible super hero. No matter how many gadgets he has, becoming equal to supers, he is still a loser. For reference, when he shows off in front of civilians after "stopping" the invading Omnidroid, he absentmindedly tosses a tanker he was levitating into the distance while power posing. The rest of his "fight" with the Omnidroid consisted of him getting pimp-slapped into a building and passing out.
The title character of Disney's Hercules, while being the strongest boy in the world, is more of a Crouching Moron, Hidden Badass - scratch that, just kind of a Badass moron - since he can't stop himself from accidentally destroying everything in his path and is mocked as a "freak" by the other townspeople. It's not until Phil takes him on as his student that Hercules becomes truly heroic.
Films — Live-Action
As seen in the page image, Evil Ed from the original Fright Night remained a loser after becoming a vampire, incapable of taking down the inept Peter Vincent. His death does serve as the film's biggest Tear Jerker, though.
Pearl the record keeper from Blade. Which leads to some slight Moral Dissonance when Eric and Karen blithely fry him with some high-tech weaponry, and Karen coldly justifies this action by saying: "He moved." (Which is itself a cruel irony, since the poor thing was so disgustingly fat that he could barely move!)
In The Lost Boys, the coven of vampires might seem "cool" at first, but in the end, only the Man Behind the Man is able to stand up to the Frog brothers, who are hardly top of the line in the world of fictional vampire hunters.
In Innocent Blood, the infected gangsters ought to be the ultimate villains you'd Love to Hate — suave mob bosses with supernatural powers — but it's pretty obvious that compared to Marie, they haven't a clue what they're doing.
Steve Rogers in Captain America: The First Avenger was the endearing kind of these when he was stuck in the USO after getting his powers. Still, it was less an intrinsic trait than the fact that nobody gave him a chance. He was forced to change the situation himself when he went AWOL and rescued 400 people from the bad guys.
Arthur Winkings from Reaper Man, AKA Count Notfaroutoe, is an excellent example of the vampire type. He was a pudgy, middle-aged, average Ankh-Morporkian whose life in the wholesale-fruit industry was interrupted when inherited a crumbling old castle in Uberwald... and with it, the curse of undeath. His wife Doreen wholeheartedly embraced the upper-crust image of vampirism, despite not being an actual vampire (jokes are made about Arthur being too old to be interested in biting his wife's neck) and not having the figure for the typical revealing vampiress outfit. Arthur, meanwhile, complains a lot about having to wear evening dress all the time, Doreen not letting him bite the necks of young women, and the difficulty of trying to build a dungeon and vault when you live in a row house, not to mention even his bat-form is too fat to fly properly.
In Sukhinov's Tales of Emereld City, there is a tale about a lazy Munchkin Bar, who after finding a magic artefact becames a king Magrab... and is as big a loser as he was before.
Akar Kessel in The Crystal Shard goes through a From Nobody to Nightmare scenario when he stumbles upon the Crystal Shard and goes from hopeless apprentice wizard to an immensely powerful potential Evil Overlord. He's still an insecure wimp who makes bad strategic decisions to show he's boss, and in the end dies because the Shard decides to rather be buried with him and forgotten for an indefinite time than go on working with him.
George is probably the nerdiest werewolf ever. Plus he's Blessed with Suck, so it didn't make his life any easier, either.
In contrast, Tully who is also a werewolf actually manages to carry it off pretty well, managing a raw animal magnetism at times and being very genial. Of course, the show doesn't really like dogs, and he's actually very emotionally scarred over his situation, having lost his family. He's overjoyed to find George, and becomes suicidal over his rejection once George finds out he was the werewolf who turned him.
There's also Seth. Even among other vampires he's something of a joke.
In fact, it's pretty much a given that almost any vampire in this show, unless he's the Big Bad or part of the show's Power Trio, is going to be one of these.
Most vampires are either badasses or nameless cannon fodder. However, Buffy mentions staking a few pimply teenage vampires who called themselves Lestat.
Interestingly, Alternate Universe Willow and Xander, as vampires, are utterly terrifying. Harmony? No. Despite them being shy (Willow) and goofy (Xander) in life, the change made them grade A Dragon material. The demon that inhabits vampires had a lot of untapped potential to work with in both. Harmony... well, even demons can't make monsters out of brain-dead mice. Mentioning "Harmony has minions" tends to be met with disbelief and giggles. Rightfully so when you see her in person trying to act evil.
Buffy herself was portrayed along these lines in the original 1992 movie (which may or may not be considered reconcilable with the TV series). Ironically, in the very beginning Buffy was very cool (at least, within the sphere of her little high-school world). But once she's informed of her secret abilities, it becomes clear that, for the moment, Buffy is just another mortal with an interesting family legacy. She can defend herself handily thanks to her uncanny reflexes, but when it comes to all the other job requirements of being a Slayer she's almost completely inexperienced at combat and has to be patiently shown the ropes by her Watcher, Merrick. To make matters worse, Buffy's habit of devoting a great deal of private time to honing her craft, coupled with her brooding about things to which most teenage girls would hardly give a thought, convinces almost everyone else at Hemery High that she's a weirdo and deserves to be treated with contempt, even to the point where she winds up a wallflower at the very school dance she helped to prepare. Add to all this her recurring fears and self-doubts, as well as her continuing ditziness (although this may well have been Obfuscating Stupidity all along), and the movie Buffy is much more an underdog hero than a superhero.
Misfits — a show about five anti-social "ASBO" teens who get magically imbued with superpowers — probably ought to be the poster child for this trope. The protagonists have got to be the biggest bunch of losers and freaks ever to shamble tentatively into the superhero sphere. Never mind the fact that they're all young offenders with a vast array of psychological issues and attitude problems, or indeed that their powers essentially mock them by creating cruel caricatures of their respective weaknesses, flaws and fears; or even that they are basically unable to mitigate or restain the (often unpleasant or disastrous) effects of said powers and have shown remarkably few signs of being able to controlthemat all. No...they also have to "re-pay their debt to society" by wearing tacky orange jumpsuits and spending their days picking up litter and washing graffiti from the walls of the urban jungle as part of their community service programme. Surely superheroes are above such things, right? Wrong.
True Blood features vampires, whose power grows as they become older, though a newborn vampire is still at least ten times faster and stronger than a regular human.
But then, there is Steve Newlin, who was wimpy and cowardly as a human and remains so as a vampire, despite his increased physical capabilities.
Eddie was a frumpy middle-aged closet case before becoming a vampire... and while he's still frumpy as a vampire, at least he's out of the closet...
There was a circus strongman character on the 1980s Disney kids' series Dumbo's Circus named Q.T. Despite his Herculean strength (he could lift a ton of rocks over his head with almost no effort), Q.T. was very shy and naive, even to the point of being a Man Child, and consequently came off as a sort of sympathetic dork.
White Wolf has this as the default for starting PCs in just about every gameline.
The default for Vampire: The Masquerade is for player characters to be this. Yes, you'll get special powers, and are vastly superior to even the greatest of muggles, but you're now caught up in a massive political system where everyone else is much more experienced than you, and every move you make is at the beck and call of beings whose power you could never reach by experience.
Promethean: The Created goes one step further — the superpowers come with the rejection of every living thing on the planet. And the planet itself.
Dan Hibiki from the Street Fighter series. He trained for years to become a skilled martial artist, and actually developed superhuman abilities — just to nowhere near the level of the rest of the cast.
We later get to meet more vampires, where it turns out this is a part of the vampiric condition in the Sam And Fuzzy-verse: All of them suffer from some kind of OCD, phobia or social maladjustment that renders them terribly uncool. While some of their members like Varney are able to present a fašade of dignity, most of them can't. In fact, Edwin's later Character Development out of this trope means he got off lightly.
Sam; made doubly impressive in that he is the last of the Lysinda Circle vampires, who get a very nice deal compared to most vampires, but he is STILL a pathetic loser.
Any inhabitant of the "Dimension of Lame" converted into a demon counts. Transformed into vicious hell-spawn or not, Poke the Poodle level evil is still an order of magnitude more vile than anything they can come up with. As one of the actual demons notes: "You turn wussy mortals into demons, you end up with wussy demons."
Vilbert von Vampire from 8-Bit Theater, a (surprise surprise) vampire who still lives with his parents and who dabbles in LARPing.
One of the superheroes active in the Boston area of the Global Guardians PBEM Universe is The Patriot, the Anthropomorphic Personification of the New England Patriots football team. Gifted with heightened strength and toughness, he was nevertheless a pretty ineffective crimefighter due to basic incompetence. The character was generally considered to be a cheap Take That on the part of creator Jack Butler, who had long held that his two favorite football teams were "the Miami Dolphins and any team playing the New England Patriots."
You see, this girl has fallen to the oldest ploy in fiction. She wants the handsome, ageless, flowing white haired vampire hunk to take her away from this dreary existence. This he does by not actually changing anything, except that now her same dreary existence will last forever, she has to eat people, and the sun will kill her. So yeah, she's totally making out on this deal.
In Worm, one of the supervillain gangs of Brockton Bay is The Merchants, led by a meth addict supervillain called Skidmark. Even ignoring the weakness of his superpower (laying an effect on a surface that pushes things in one direction) he's considered a total nobody — as of the start of the story, his gang only 'holds territory' that no-one else is interested in.
More pathetic yet are Leet and Uber, a pair of supervillains with a video game schtick. One reason for their lack of success has to do with their powers—Uber can mimic skills at near-master levels of normal humans, while Leet is a tinker whose devices tend to malfunction—but a larger reason is that they are just incompetent. This pair is mocked as being incompetent by pretty much everyone in the story.
The Consortium in Generator Rexafter each of them gets one of the Meta-Nanite powers and become living robots. Even with their awesome superpowers they are still a bunch of petty middle-aged men with no real clue how to use their powers effectively. They rely entirely on Black Knight's lead to accomplish anything.
In Lilo & Stitch: The Series, there's Experiment 625, Gantu's wisecracking Bumbling Sidekick. Despite possessing all of Stitch's abilities, he's so incredibly lazy that he would rather sit around and make sandwiches all day than help Gantu catch the other 624 experiments let loose in Hawaii. In fact, 625 often lets himself get humiliated by the eponymous heroes as well as other experiments rather than exert himself in any way (at least until the movie, Leroy & Stitch came along).