Dio just wants what is rightfully his. And damn those that happen to stand in his way.
At first, he wants a warm meals, and damn those idiots for thinking they're better than him at chess. They don't deserve their coins if they can be beaten by him.
(Oh, and if his father, Giorno, could die so he would't have to look after him anymore, that would be swell.)
Then, when he gets adopted by George Joestar, he wants the Joestar family fortune. And damn that idiot Jonathan. That bratty upstart, who hasn't had a hardship in his life, doesn't deserve to have that just handed to him.
(Oh, and if George could die to expedite the process, that would be swell.)
An finally, once he becomes DIO, he wants... Well, it doesn't really matter any more. What ever he happens to want, he certainly deserves. He's no lowly human anymore. He's the most powerful being on the planet and what ever he wants (including human life, which he views at the level of cattle at the time) should rightfully be his. Since might makes right. Right? And anyone who got in his way, well they just damned themselves, didn't they?
(And if the pesky JoJo would finally die and stop pestering him with that annoying Hamon of his, that would be swell.)
That would answer the direct question, but I don't think that's quite the answer that would satisfy you.
No, Dio isn't exactly a complicated character. He doesn't have some almost but not quite justifiable reason to do his evil. He barely has a tragic backstory. (Well, it is quite tragic, but it doesn't really factor into his character or motivations.)
But that's not what people adore about him as a villain. Dio is a pure, uncompromising and utterly unredeemable force of nature villain. And his sheer confidence and utter dominant force is what draws readers to him. We know that by all accounts Dio should win.
Not because we want him to (I think that's what you are looking for from him when people said he was "an amazing villain"), but because we know just how powerful an entity he is. The heroes shouldn't stand a chance against him. And damn is he stylish and confident when is tossing the heroes (and random bystanders) around.
Who else, but Dio, would compare human lives to break?
Who else, but Dio, would torment Polnareff by repeatedly carrying him down some stairs?
Who else, but Dio, would command a random store assistant to bring him his severed leg? (And then kill her.)
Who else, but DIO, would think to finish his opponent by dropping a road roller on them?
He may not be the most complex villain there is, but he's far from a boring one.