The Witcher has some of that. Your group is famous because your profession is important, but your character isn't that well known personally and many people are pretty creeped out by Witchers and don't really like them.
Not well known? Well, Dandelion made Geralt a living legend across the world/borthern kingdom during his living times already...
As to the OP: In some ways, I agree. I do not mind my char becoming a hero though ... but I prefer this to be a result in the end. Being a powerful figure like the Inquisitor is fun, but I don't want this to be in every game (next time: King/Queen of Nevarra? No, please not).
In that regard, Origins once more is my preferred way of story. You start rather "low" (yeah, Couslands are noble, but you know what I mean hopefully), you have no real "super-warden-powers", and for the most part of the game its you against all odds, until you gradually earn more power and it is therefore in the end lot more satisfying (seeing those dwarves and elves and soldiers storm Denerim etc...very cool, YOU did make this happen!)
So for the future, I first hope for a big Expansion that deals with the Inquisition. I don't want the Quizzy to die, but I want to see the Inquisition itself lose much power and relevance in the end. It seems way too powerful at the moment. The Inquisiton has to go/become weaker so nobody constantly asks in the next game "Why isn't the Inquisition handling all this, they are so powerful!"
The next big game three years down the road? Should focus once more on the Grey Wardens, Darkspawn and the blight again, I am tired of the demon-sfuff. And the main char should simply be a Warden-conscript again. I want to see Anderfels and Weisshaupt, and I want to see the Order crushed there right at the start. I want my heroine flee with her rag-tag-group from burning Weisshaupt, trying desperatly over the course of the game to find a way to stop the horror of the main-plot while the world hardly realizes she is there at all, as whole nations are more pressed to fight for their survival. No agent-cards, ressource-gathering, managing a stronghold...Classical RPG-stuff like travelling and living on the road and all that.
And another idea that probably would never happen but might be cool if done right: A somewhat "smaller" game in terms of storyline and stakes before the main-game, to fill the gap somewhat. Something like DA2, an elaborate Origins-story for the upcoming new hero, where we play him/her as simply a warrior/rogue/mage in some city + outskirts and deal more with plots, the shady underworld and stuff like this, without too much supernatural fade-stuff and all. It wouldn't be so much about world-changing events (though they would be mentioned and foreshadowed already, of course), but more about personal things.
And this "prologue" game would be the foundation of the next big thing, when our "smaller" hero is conscripted to the Wardens and the epic game begins. This was as I still suspect, the abandoned strategy behind DA2 and Hawke all along. And if done right it could be a cool way to tell a story and give our own chars the depth many people are missing from the main Quizzy