I don't find great (not even good) the fact that the Hero of all cities of Cyrodill, who personally and all alone closed most of the Oblivion gates, who was one of the closest (if not the only one) friends of Martin Septim the last Emperor of the Third Empire and the Septim line, who if not for him/her Nirn would have fallen in Dagon's hands, became the damned daedric prince of freaking madness.

Do you?

It's a beautiful irony and goes well with him and his ties to Pelinal.

It's just his continued madness after mantling Pelinal...Makes perfect sense or to me at least.

The Champion entered who knows how many Oblivion Gates - witnessing untold horrors in the Deadlands and Mankar Cameron's "Paradise", seeing the deaths of not one but two Emperors, the slaughter of friends and possibly loved ones. He witnessed the aftermath of the slaughter in the Chapel of Love, did battle with an ancient Elf Warlord - again witnessing many friends and companions fall along the way - and ventured to an entirely different plane of reality to fight against its impending apocalypse.

Going Mad from all that was a foregone conclusion.