It's cool you used Transfigurations as your example. I think that was the third fic I ever read, and it just blew me away! I think I could recite the sex scenes to you even now, because it was so well done. But not over-beautified. One of the most memorable things to me was when they stopped in mid-screw and changed positions. And I was - oh, that's so realistic.

I must admit, though, I've read a few fanfics that think the opposite: that the story is all about the sex. And rather than feel aroused, I'm just plain bored.