mostly it's about power play and forbidden love/lust. and access. it does help that everyone lives under the same roof. but no one is gonna make their story about incest just to put them under the same roof.
it's rarely about incest itself, most of us are disgusted by sexual thoughts about our real relatives. but the characters are not your relatives, and I don't mean that in the 'pixels can't be fucked' way.
the disgust we feel about incest is a result of biological imprinting you get at 1-3 years old, and it only exists for your real family. sometimes adopted people never got that imprintation, and later in life meet their mom or siblings and they end up fucking because the disgust simply isn't there. of course the social stigma is still there, but if you put those people into a situation where "who's gonna know? I'm not telling if you're not telling", stuff happens between attractive people. then we get the tabloid headlines. put relatives separated at birth on a deserted island and return 5 years later, and I guarantee there's gonna be children.
but the mom in the game is not who you grew up with, so you have no imprinting for her. there can of course be other reasons to be disgusted by it, like abusive power relations. but it's not from your biological imprintation. everything else is still there, the cultural taboos of being related, having age gap, and authority gap. but not the imprinting you have for your own family. all of them obstacles for forbidden love, which just makes it hotter.
so it's not really about wanting to fuck your mom, sister or daughter. it's about power play, usurping authority, unattainable conquest, domination, degradation, even abuse fantasy. it can range from a sweet story of forbidden love to corrupting the purest love of a mother or daughter into a mindless fuck hole. it's extremely versatile, everybody knows each other already and are accessible, and have an element of 'default love' that works great at justifying things.
but the dynamic varies a lot depending on the pairing, and I think it's one of the reasons why people have strong camps over what kind of incest fantasy they like.
in a mom/son story it's usually about son pursuing the most unattainable woman possible, his own mom. an unrelated milf might be similarly out of his league, but his mom takes that unattainability a couple of levels higher. also it helps that it's the weaker party pursuing the stronger, which makes it less abusive.
when it's mom pursuing son, it's usually more about mom having feelings she doesn't want to admit but will ultimately cave into. passion, obstacle, breakthrough. it's a little more abusive, but also mitigated by the fact that we generally feel every guy scoring some pussy is a hero of his own life.
between siblings it's very neutral, there's no other obstacles creating tension except being related. no age gap, no authority gap, nothing. no inherent abuse (unless you write it in, but it doesn't come from them being related), which makes it almost vanilla.
dad/daughter usually has a similar vibe to mom pursuing son. he knows he shouldn't do it, but he will. and the daughters are invariably sweet and naive, childish, and more than a little retarded. (the sons are no einsteins either, but the mom never has to teach them how to jerk off). the daughter is similarly unattainable as mom is, but the dad has all the power in this relationship. which makes seducing the young, naive and trusting daughter like shooting fish in a barrel as far as obstacles go. the dad/daughter setting is also inherently abusive, because we don't feel the daughter being a hero of her own life by scoring some dick. so unless you lean into the abuse full on, the obstacle for the writer is to not portray dad as rapist. it's a very different dynamic from mom/son case.
daughter chasing dad makes it very different, but we rarely see that as pretty much all female protagonist games are rape simulators. most likely the daughter just gets raped by a disgusting old dad, instantly turns into a slut, and can't stop inhaling dad's old man balls. these tend to be the most abusive, and the dynamic is about being treated badly and loving it.
one more thing about trying to understand kinks you don't share. the point with many kinks related to negative behaviour is NOT how it would unfold in real life, the kink is the opposite: the fantasy is not about mom/sister/daughter being abused, developing serious mental problems, depression and killing herself because she just can't go on. the fantasy is about her LIKING that abuse. it would never happen. but the fantasy is what IF it could?
