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 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 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.
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.
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.)
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 either of those, aside from the Potted Plant, usually has some quite nasty side-effects, as it should be impossible (even though, considering the nature of the game, it isn't. On the contrary...).
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.