I honestly can not think of one.
Failure isn't amazing. Failure sucks. Failure is what I strive to avoid and work to prevent next time.
I am a bit of a perfectionist, with a low self esteem issue. This means failure eats away at me until I do something about it. I can't view a failure as anything but a flaw to be corrected.
I've certainly had moments in games where a botched roll has lead to hilarious results or a misunderstanding about what was going on in our story game us a fantastic moment when it finally dawned on us what was actually happening but nothing comes to mind right now as an amazing failure.
Sorry but that's the best I can do with this one.
I hope I at least gave you some insight into my thought patterns.