Something not limited to any game, but especially in RPGs and war-games, would be the "wipe" or "total party kill", in which some way somehow you manage to lose every member of the squad all at once.
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 annals 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.
There was a game where a Dark Eldar player fielded a Talos with the express purpose of using it to draw lascannon fire. During the Imperial Guard's first Shooting Phase, it took four lascannons to the face and died.
Then there was the infamous "Living Saint Debacle" from White Dwarf 294. In a battle report between the Sisters of Battle and the Tau Empire, the battle had been relatively even up to turn 3, but then the Sisters' assault began to hit home against the melee-vulnerable Tau. Their most powerful unit, Living Saint Justine, had not produced great results to this point, having already taken a wound from enemy fire, but she'd gotten stuck into the Tau Broadside Battlesuits, even if she'd only managed to kill a single Shield Drone in the first round. In the next round, however, the Battlesuits hit back. Unskilled, but Strong due to their Powered Armor, they managed to hit her twice on 5+ and wound her with both hits. Not a problem, she has a 2+ armour save and 2 wounds left. Snake eyes. Oh well, these things happen, but all she has to do is pass an unmodified leadership test on Leadership 10 and she gets to be taken off the field and placed in reserve for later redeployment instead of dying. Double 6. Oh dear. Finally she has the drawback that when she dies, the Sisters of Battle army loses D6 Faith points from their pool (used to power their Act of Faith abilities) AND if this roll brings the total Faith points to 0 or lower the Sisters cannot earn ANY more Martyr points from units dying for the rest of the battle. The Sisters player had exactly 6 points remaining at the time and by this stage everyone present KNEW what his roll would be. Yep, another 6. No more Acts of Faith for the rest of the game.
Joe Sturge: What in the name of the Emperor happened? You really can't legislate for a 201-point special character being lost to an orgy of bad dice rolls. My plan had now flown out the window.
As far as Warhammer examples go, there was the time a (reasonably large) unit of Dark Elf Spearmen not only lost a fight against a Goblin artillery crewnote war machine crews in general (except 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!
And then there's the Tomb Kings versus the High Elves. The White Dwarf staffer playing the High Elves sends his spearmen (who took some losses in the first turn) to drink from a Wyrding Well. These wells have a 1-in-3 chance of poisoning you (costing that unit its action for the turn), driving you mad (giving the unit Stupidity and Unbreakable for the rest of the game), or healing you. He rolled poison. What makes this an epic fail is that both of his wizards were in the unit, costing him his Magic Phase. This act was singled out by both players as the move that cost him the game.
One of the highlights of the Skaven army is the number of things that can go hilariously wrong, perfectly illustrated in a particular battle report against the Empire. A Skaven Assassin leapt out of hiding to hurl a Warpstone grenade at a Steam Tank, fumbled the throw, and had to test on his ninja-high Initiative to avoid blowing himself up. The Skaven player made the mistake of joking "Anything but a one!" Despite such setbacks, the Skaven accumulated enough victory points to squeak by with a win, until the Skaven player remembered that one of his characters had a magic item that enhanced stats with the low risk of killing him post-battle. "Anything but a one!" The game ended in a draw.
In the BattleTech 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.
A variant of the Hollander seems to have been an Epic Fail in the making when it was being built. It is quite possible to destroy itself without receiving a single shot of enemy fire. The Heavy Gauss Rifle requires a piloting roll to stay standing, and the rear armor is insufficient to withstand falling damage. If the pilot fails a roll and falls onto its back on the side with the gun, the gun can detonate, doing exactly enough damage to tear the 'Mech apart. An Epic Fail in both design and action.
Yugioh has its Card Game where players need to keep in mind the end goal: to win/make their opponent lose. Doesn't stop players from losing sight of the end goal and making stupid plays.
One player was playing a Deck in a major tournament that included the card "Caius the Shadow Monarch". The player used its effect to remove an opponent's card when if they removed the same monster it would have done enough damage to win the match, instead they lost completely. (To specify, if Caius' effect is used on a Dark monster, the opponent takes 1,000 points of damage. Caius itself is Dark, and its effect can be used on itself. If the player had done that, and not used the effect on his opponent's monster, he would have won.)
An alarming number of players report witnessing someone activating Dark Bribe (negates a Spell/Trap card, but lets the opponent draw a card) in response to the opponent's Upstart Goblin (lets the user draw a card and gives the opponent 1000 Life Points), essentially using a card that could have saved them in a tight situation to deal 1000 damage to themselves. Another commonly cited example is using the effect of Maxx "C" (for one turn, lets the player draw a card every time the opponent Special Summons a monster) in response to the opponent's Pot of Duality (which prevents the player from Special Summoning for one turn).
In Munchkin, it's actually possible to be defeated by a range of Level 1 enemies. This includes the Potted Plant, Dirty Laundry, Graffiti, Footprints... and a Goldfish. Losing to some of them even has some quite nasty side-effects, since it's highly unlikely to lose to a level 1 monster. It's not impossible, of course, since with the game's monster modifiers in play you might end up fighting a Humongous, Ancient Goldfish and its Mate...
In Mistborn Adventure Game, if you fail a roll, you can earn Complications, which cause a wide variety of penalties (losing Resiliences, losing Standing, losing a die from your next roll, your opponent adding a die to their next roll, etc.) if not bought off with Nudges. If a player gets three or more Complications on a single roll, the Narrator is encouraged to slap a penalty on the entire team instead of trying to find three different ways to punish you. So in Mistborn Adventure Game, you can fail so epically that just knowing you screws people over.
This is an inevitable result of playing Blood Bowl for too long: Sooner or later that 1 you roll won't just be a failure but the first step of a Disaster Dominoes of bad rolls. It is entirely possible for your level 7 superstar player to kill himself by running too fast and tripping, or by failing a three-die block on a snotling.
In the background of a Pathfinder Roleplaying Game module, ancient Azlanti astronomers attempted to use magic to Terraform the moon. Instead, they accidentally opened a portal to the Abyss that unleashed a cult of demons that turned the moon into their headquarters as they plot to take over the world.
The majority of plans in Fiasco end in some kind of disastrous failure, some of which rival this scale. To drive the point home, the page quote is actually cited within the text of the rulebook. This can be especially impressive if someone goes for an out-there Tilt result: someone's plan to steal bags of cocaine from the Mafia, or something, can end disastrously in a bear-related incident, for example.
In Anima: Beyond Fantasy, in order to invoke the Arcana "The World, Reversed", a character must have failed at every major undertaking in their life... and then fail the roll to invoke said arcana.