In the year prior to Destiny, Picard faced down the Borg three times. Every time, he beat them by the skin of his teeth, but then they came back again, giving him barely a respite. He became afraid that he'd never be free of them. And then, when he finally thought he'd achieved that freedom and decided to take a chance on starting a family, the Borg invaded en masse, a seemingly unstoppable force. And it broke him. It was just too much.
You can talk all you want about who Picard is, what he would or wouldn't do, and you'd be right in any normal circumstance. But we know that he changes, weakens, when he's faced with the Borg, because of the damage they did to his psyche. And the rash of Borg attacks in 2380-81 was just too much for him. It overwhelmed him, filled him with despair, and he lost his way. Yes, his behavior was out of character for Picard, but that was the point. He wasn't himself.
I personally thought it was a daring and unexpected direction to take his character, but absolutely necessary within the story. The invasion was so severe that there had to be someone who just went crazy and gave up, to give it the necessary weight. Having that person be Picard, generally the whole universe's most stable and heroic character, brought a real gravity to the whole thing.
People don't think in abstract numbers. Several billion dead is unlikely to mean much to a reader; with casualties like that, people's minds just turn off. I think Picard giving up lent a visceral knowledge of just how bad this was that nothing else in the trilogy did, making the moral victory at the end stronger.