The FDS wasn't a failure, it sold quite well even though piracy became a problem with it. Also the N64 never had a CD add-on, your thinking of the 64DD which was a disk drive add-on like the FDS but used basically Zip disk's for software.
As for biggest failure of a console I'd say the Jaguar even though I do like the system. I think it was just too little too late for Atari at that point to make a comeback.