Nintendo games (Mario and Zelda especially) always have magnificent, epic, sweeping compositions. I think the work in the Halo series (though I am not a fan of the games) is pretty damn memorable. The Metal Gear Solid games also have epic and emotionally-charged music. But most games are pushing to all-action (FPS, usually or GOW type 3rd persons) or casual gameplay (Wii Fit) and unless that casual game is Rock Band, both genres do not lend themselves to memorable or strong music. When you play BioShock you are playing to shoot everything that moves in an epic art deco atmosphere. Unless it's some retro rendering of Under the Sea, you're not going to notice the music. Did Gears of War even have music (that was a joke)? Resident Evil has never been known for strong music (albeit I thought RE2, CV and Remake had some nice touches), but RE5 had generic '90s action music blaring because it moved into that action direction and as those along with casuals make up most gaming, there is no need for anything that creative to be played in the background.
With that said go purchase Super Mario Galaxy, The Legend of Zelda: Twilight Princess, Metal Gear Solid 4 or Halo 3 and enjoy some epic arrangements.