In Magic: The Gathering, this is pretty much the goblin race's hat. Expect them to carry grenades over to their enemies, launch themselves out of cannons, and other hilarious deaths. Oddly, this even applies to things like Skullclamp, where there's an assumption that the head has something in it before you crack it.
In Exalted's backstory, the Sidereals' desperate attempt to hide that they masterminded the overthrow of Creation's god-kings broke a constellation. It's worth repeating that: they broke a constellation. No one even knew that was possible, and it's a failure that has not been equalled since. (In canon, anyway.)
RuneQuest has a melee fumbles table, apparently based on the experience of reenactment groups. A surprisingly high number of rookie combats end when one of the duelists chops their own head off.
The Battle Reports in White Dwarf magazine often feature examples of hilarious fails due to very bad dice rolling, but the standout example of this trope was an Ultramarines captain in the inaugural battle report of Warhammer 40,000 5th Edition. He lost his first Wound when a tank he was about to charge exploded in his face, then decided to make up for it later in the game by taking on Abaddon. As he charged in, he rapid-fired his Plasma Gun... and proceeded to overheat with both shots. And fail both his saves. (That's four 1's in a row, by the way) Yep, he was dead without any enemy input whatsoever. The players joked he may well be scrubbed from the anuals of Ultramarines history.
There was also that Apocalypse game where Cassius tried to lob a Vortex Grenade at Abaddon, only to miss horribly and hit his own Chimera.
Abaddon had his own when the Blood Angels got a Battle Report to celebrate their new codex. After a heavy volley of fire, only one hit landed on his unit. The player decided to have Abaddon roll the save (Abaddon's saving throws are what you expect for the strongest Chaos Lord in the setting)... and took a wound. This signaled the beginning of the end, as the unit was promptly wiped out by a single chaplain (to be fair, it was Lemartes), with Abaddon surviving only long enough to be smeared across the ground by the Sanguinary Guard.
As far as Warhammer Fantasy examples go, there was the time a (reasonably large) unit of Dark Elf Spearmen not only lost a fight against Goblin artillery crew*
war machine crew in general (expect Dwarfs) tend to flee if an enemy even attempts to charge them, and Goblins are not only even weaker but fear Elves
, said crew actually managed to kill the unit's Sorceress!
In the Battle Tech community, there is a term for this as applied to dice rolls - Hellbie dice, where a roll or series of rolls is so utterly catastrophic as to completely ruin a player's chances in a game and goes beyond mere Critical Failure by defying the laws of averages. Named for JadeHellbringer, global moderator for the main forums for Classic Battletech and regular at the Battletech tables of several conventions. In one oft repeated instance, he played a game where he was given a 'Mech with ten Ultra autocannons. This type of gun can be fired normally, or with a double mode activated that doubles the firepower at the risk of the gun jamming itself into utter uselessness for the rest of the match. Any given gun has a 1 in 36 chance of failure (2 on a roll of 2d6) when fired on double mode. While firing all ten guns on their double setting, he managed to jam seven of them on his first turn. The utter defiance of averages in favor of astounding failure is a hallmark of his history with dice.
For the record, the odds of rolling that badly are approximately 1 in 700 million assuming unbiased dice.