Let's see. First, I'll have to mention System Shock 2 and Deus Ex, though they are apparently a bit more popular than I thought when I first played them.
Likewise, the X-com series, which seems to be one of those gems that a decent size group of the population knows about and everyone else stares at you like you're retarded when you bring it up.
The Master of Orion series, which by the fact that there is three of them seems like it was popular at some point, but no one I meet ever seems to have heard of it.
Master of Magic, a game I have yet to meet ANYONE other than my brother who has actually played, and a game so good that both of us have gone to great lengths to get this old DOS platform game to actually run as recent as a couple months ago... That's definitely a record for me, at least, to still be playing a 15 year old game that is now a pain to run due to compatibility issues, but it was that fun.
More recently, Mage Knight: Apocalypse, which after playing it for a couple hours is pretty obvious why not so many people have heard of it. Though not truly shitty, it's pretty damn shallow, and couldn't convince me to care about anything it had to offer.