Humor in RPGs is hard. I've said before that when I end up running horror for an extended period of time, I start to drift into Evil Dead 2/Army of Darkness territory. That may not always be ideal, but at least there are some "guard rails" for when and how the humor comes out.
For example, in the above example, I often end up playing up the impossible amount of gore, people that should be fazed by it that are humorously not fazed by it because they have hit a breaking point, and villains that are ridiculously cordial as they are doing horrific things.
The risk with the humor, in that point, is that the players don't pick up on the fact that you have buried the needle on how horrible things are, and are similarly aligning themselves with all the people that are unfazed or acting "too" normal.
That said, even if that doesn't work, it fails to convey the proper tone, rather than actually conveying the opposite tone. There are tons of humorous mistakes that can do the exact opposite, and start eroding broader points you are trying to make.
You don't want to undermine the competency of your heroes in any game that is going to have even the vestige of a serious tone. Even using the above example, many of the times when we see Ash Williams really screw up, it's not because Ash isn't good at killing deadites.
Ash screws up because he makes a selfish, short-sighted decision, or because he gets arrogant about how difficult a task actually is, and won't admit it is outside of his skillset. Even then, soon after he screws up bad, he usually has an over the top moment of awesome.
That moment of awesome may not solve the problem, but it reminds you that Ash still has an area of competency, it's just not making wise decisions or evaluating his own strengths or weaknesses.
The takeaway here is, if you are going to find humor in a player character's failures, don't do it in a way that minimizes what that character is actually supposed to be good at. I'm looking at GMs with this one, but also players that undermine their own core competencies.
If your character has a high survival skill for their level, and they fail to track someone, they should fail because something unforeseen, or even how awesome the opposition is. If they have a penalty to deception, & blow a roll, sure, that could be about amusing incompetence.
It's also probably a good practice, if you are the GM, to let the player define how it looks when they fail, assuming you don't just want to frame it as the circumstance being extraordinary, or the NPC being competent opposition.
When it comes to not undermining the villains, don't play anyone for laughs that needs to look competent. Even if every PC drops below zero hit points and they just barely survive the fight, if you portray the villain as silly or incompetent, they will have that impression.
It won't be the impression the numbers gave, it will be the actions and the tone and tenor of how the NPC reacted to what they were doing. Maybe you want your villain to be accidentally competent, but then you need to set up the external source of their competence.
As an example from my DC Adventures game from years back--the penultimate major villain the heroes were dealing with was the Time Trapper. The rolls in that fight were that much different than earlier fights with Circe or Mongul.
However, two of my six players were familiar with some incarnation of the villain from the comics. Neither of them took the villain seriously, and both spend the whole fight reframing every missed roll by Time Trapper or solid hit from the characters as Time Trapper's screw-ups.
The final, for real this time, villain of the campaign was Darkseid (like I was going to run DC Adventures for a year and not set that up), and I rolled terribly for him. The PCs tore through the fight relatively quickly, and I was a little disappointed.
That said, more people at the table had an impression of Darkseid, either from comics or the animated series, and none of them had an impression that made him seem incompetent, so even they chalked up their victory to getting very lucky and dodging a bullet.
Your main villain needs to be good at what they do. They can have quirks and foibles that are humorous, but those can't be along the same axis of what they are actually good at doing.
You can give your villains humorous minions, but even then, you have to be careful about the message you are sending. If your villain is sitting at the head of a powerful organization, even if they are personally good at fighting and building gadgets . . .
No one is going to take them seriously as a would-be ruler of the world if they can't keep their own organization in order. If your upper tier of Hydra is Baron Strucker and Viper, then it's fine to have Bob the Hydra agent show up.
Jarlaxle can joke and flirt and do things for the fun of it, because he's an excellent duelist, & because he's proven that he can survive drow society and turn a profit in multiple venues. Any humor he provides doesn't detract his core competencies.
Like Darkseid in the above example, some villains have some automatic buy-in if anyone knows about them. Even then, you have to keep an eye on how much of that villains reputation you are "spending" with your humor.
The Xanathar can be crazy & worried about his goldfish because his competency is established by being a floating aberration with eyes that can kill. But if you portray the organization as not taking him seriously, or the authorities ridiculing him, and you start to see errosion.
In this case, look at Joker. He's often portrayed as amusing and whimsical, even when he's doing horrible things. But good writers often offset this by playing up how scared his henchmen are of his unpredictability.
His core competency is being a destructive wildcard, and his minions reinforce this by trying not to upset him or draw his attention.
Why did I launch in to all of this? Partially because I was reading some one shot adventures, where creatures that should be the scary harbingers of greater conspiracy are given two potential scenes in the same scenario that are essentially played for laughs.
Unless you want the overall conspiracy to feel like a joke, and for the adventurers to feel like there are no stakes to this one shot, you can't afford to spend this much time on humor. You can only do that if the whole scenario is going to be played for laughs.
The other reason I mention this is because today is Aaron Allston's birthday, and I'm reminded of the Wraith Squadron books. I loved the books--eventually. But when the Rogue Squadron books first transitioned from Stackpole to Allston, I wasn't sure if I was going to be a fan.
There was more banter and joking between the cast. Their former careers were less "displaced average folk" and "defecting military" compared to the established crew, and some of the careers felt a little over the top.
Warlord Zsinj felt much more like a blustery blow hard versus villains like Thrawn and Isard in other Star Wars books. But in the end, I really loved the Wraith Squadron books.
If I hadn't decided that I was in it for the long haul, I wouldn't have seen the stakes get raised in the books, the opposition increase, and the backgrounds that seemed humorous or over the top come into play and develop more nuance.
It was a tricky set-up, because Allston frontloaded a lot of the humor when he took over the series, and "spent" some of the capital that was earned by the Stackpole volumes, and even though it paid off (at least for me), it was a risk.
It was definitely a calculated risk, since Allston wasn't just a novelist, but was also a long term game designer, and someone that understood the underlying tropes in various media, but not everyone would have been willing or able to spend just enough capital to pay off.
