Sosuke Aizen, the Big Bad of Bleach, is a big offender of this; one could even make the case that this was invoked to justify this behemoth's downfall. He more or less singlehandedly takes out the entire cast with a mixture of Magnificent Bastardy and being one of the strongest shinigami in existence, but as the final battle approaches, he devolves in a fairly straightforward monster (literally) who tosses aside his lucidity in favour of brute strength. It doesn't end well for him.
Mobile Suit Gundam AGE: Zeheart Galette seemed to get more and more stupid and crazy as the third generation went on, culminating in ordering Fram Nara into a suicide attack that didn't work, and then killing some of his own men. He then took to a Mobile Suit battle in a blind rage and got killed in ten seconds, despite having both a machine and the piloting skills to make him a match for the main characters.
Film - Animated
In Hercules, Hades has managed to get Hercules to agree to give up his super-strength in exchange for Megara's survival and safety. So, what does Hades then do? While he's taking over Mount Olympus, he sends a giant Cyclops to attack Hercules, who will no doubt be with Megara, thus making it highly likely that she will be harmed in the crossfire. And since the deal is immediately nullified if she's harmed, he has effectively ensured that his plan will fail.
Even better, the reason Hades did all this was because of a prophecy that was stated verbatim, "Should Hercules fight, you will fail." His super-strength was notstated as a requirement. So after sidelining Hercules, he then sends a giant Cyclops to basically ensure that Hercules does in fact fight. And naturally, Hades's plan fails.
This happens in Scooby-Doo! and the Witch's Ghost. The gang go to help a man named Ben Ravencroft clear his ancestor's name because she was accused of being a witch. Once they find her book, he reveals that she was one. As he tells them that he's going to release her from the book, get magic powers, and basically destroy all, they only give him the "You won't get away with this" speech and WAIT FOR HIM TO SUMMON HIS POWERS!! Of course, they're not going to tackle him before he reads the book aloud, otherwise the movie would be over.
Sakharine of The Adventures of Tintin is a very smart villain, preparing for many different disasters, keeping enemies alive only when their information is needed, being careful to not commit open robberies of the MacGuffins, and pulling a I Surrender, Suckers on the good guys. Then when threatening to destroy the treasure map, he chooses to monologue at length about how the heroes have failed to beat him, buying enough time for Tintin to sneak around and steal it from him.
Film - Live Action
Quite a few thrillers end this way.
The original ending of A Perfect Murder (the remake of Dial M for Murder) was an aversion of this. The wife shoots her husband for trying to have her killed, and fakes a struggle. Since she had evidence he planned to have her killed, the police shrug it off, and she actually commits the perfect murder. But it was decided that the audience couldn't morally identify with her, and then we had the wife doing this trope.
Pretty well describes the Colonel in Avatar up until then he's very level headed and conscientious of the dangers posed by the alien planet, but suddenly he loses his shit and decides to attack the stupid space elves on ground in which all his forces are at a disadvantage instead of just sitting put on the strategic assets it's his job to hold and that he already possesses and just wiping them out with his force multipliers when they have to come to him anyway. You could argue that it's suposed to be Revenge Before Reason but nothing in his methodical professionalism or grim determination before that point really suggests that the betrayal of a single subordinate would have that effect on him.
Once upon a Time in Mexico: The Femme Fatale spots her ex-boyfriend wounded in the street from a distance; the sensible thing to do would be to snipe him, as she was perfectly capable. But instead she goes down to talk to him, so that they could have a dramatic final exchange and so that Sands could shoot her.
None of the characters in Hellboy II make intelligent decisions in the third act, which lead to the villain getting the MacGuffin and the Love Interest dying (although critics felt it didn't hurt the overall film).
The movie Underworld when the Big Bad has rendered the hero completely helpless and has him on the floor at his feet... but then walks away to deal with another problem. The hero of course recovers and saves the day.
Of course, the Hero is the spitting image of his dead daughter - a daughter he'd already been mortified by having to kill before. It was possible he wasn't able to bring himself to deliver the killing strike this time.
Thrawn in his Star Wars trilogy. The author has openly admitted that he couldn't think of any way, realistically, for the good guys to win against the relatively intelligent and cautious master strategist, and that he had to take up the Idiot Ball for the last few chapters to give the story its happy ending.
Live Action TV
Harmon Rab and Sarah Mackenzie of JAG are often guilty of this. When it came time for the episode climax, they would often confront the bad guy alone. They are partners, but they seem to forget that when it comes to one of the very reasons law enforcement has a partner system.
Especially since they are lawyers and not NCIS agents. Mac was a Marine, and a badass one at that, but still.
Charmed Zankou, S7's Big Bad is smart enough to come up with some cold-blooded torture that weakens the sisters' confidence enough so he can steal the Book of Shadows. Then in the finale his IQ seems to plummet and the sisters manage to goad him into doing something stupid so they can attack him. It was a shame because he was one of the few worthy opponents they had up to that point.
Part of the fun of Columbo was zig zagging this. He's seem like a fool, and even seem to fall into this, but it's all an act.
The irony is his act is an attempt to invoke this trope, seeming careless, stupid and oblivious so his enemies will slip up and Columbo can nail them.
The Big Bad in the Doctor Who special "The End Of Time" tells the Master in the most insulting terms that the moment his plan (which the Master is an essential part of) succeeds, the Master will be killed. While the Master is standing next to the machine that forms another essential part of the plan. He also passes up numerous opportunities to shoot the Doctor despite having previously shown a willingness to kill people for disagreeing with him; and the Doctor isn't part of his plan.
In the original series story "The Brain of Morbius", the Doctor defeats and captures Morbius by the middle of the last episode, deciding to remove his brain and return it to the Time Lords. It's fairly logical for him to threaten Solon, the Mad Scientist responsible for giving Morbius his new body, into doing the job for him. It's less logical for him to leave Solon alone to do the job and go and check on Sarah in a room with a lock on the door. To the surprise of no-one but him, Solon locks them in and revives Morbius.
In the Criminal Minds episode "Our Darkest Hour", the detective helping the team on the case, Matt Spicer, experiences this and forgets protocol at the end of the episode, leading him and Morgan into Spicer's house without waiting for backup, allowing the Unsub to trap them, kill Spicer, incapacitate Morgan and kidnap Spicer's youngest daughter. It's justified in that Spicer was dealing with an issue in his family, which the Unsub had targeted once before and killed everyone except for him.
Hugo Strange in Batman: Arkham City. He spends most of the game and the prequel comics matching wits with Batman, who has a lot of trouble genuinely messing with his plans because Strange has studied him so thoroughly. In the last third of the game, Strange takes a hostage over a video screen, forcing Batman to stay still while he lectures him and his goons fill the room. Instead of forcing Batman to not fight back for the sake of the hostage, Strange just lets him go, allowing Batman to knock out all of his men. And despite how familiar he is with Batman's tactics, when Batman begins climbing the tower to his control room and his thugs can't locate him, Strange just assumes he's fallen off.
Yoshiyuki in Da Capo II develops this during the Koko route. While he's never super perceptive about love, in this route he basically goes out of his way to be as incompetent as possible about the matter until the point the stupidity starts to edge into jerkassery. The most likely reason is that there's no real dramatic story set up for Koko; she loves Yoshiyuki, pretty much always has and has nothing seriously wrong with her life.
In Fate/stay night, this is the only weakness of Rin. She is good at the third most important thing, the second most important thing, but she always screws up when it comes to the first.
In the finale of Avatar The Last Airbender, Zuko tells Katara that he can beat Azula alone, which turns out to be true and is about to one-hit Azula the same way he did a few episodes back with his father (and at that time he was unprepared). Then he makes the mistake of taunting her. Katara makes the mistake of standing too close to the action. Result? Azula fires at Katara and Zuko is taken out of commission shielding her from the lightning, leaving Katara to finish what he started.
It wasn't the taunt in of itself that was Zuko's mistake. He taunted Azula for the specific purpose of drawing out her lightning attack. You can even see Zuko preparing to redirect the lightning. Zuko's mistake (aside from not just swallowing his damn pride and not attempt to get Azula to use lightning just so that he can show that he can redirect it now) was that he underestimated how lucid Azula still was. Even in her Villainous Breakdown, she still thought clearly enough to realize that targeting Katara, not Zuko, was the best move.
Luckily he survives, and because there are standard sportsmanship rules in an Agni Kai, Zuko still won the moment she attacked a spectator.