Gold is just a token to represent arbitrary resources (e.g. food, weapons, labour). This is assuming we're looking at the desire for gold as a medium of exchange and a store of wealth (rather than people wanting it for its intrinsic properties - e.g. it's pretty in jewellery).
1. It's not trusted or needed as a mechanism of resource exchange
It's no good giving someone gold for something, if the receiver doesn't believe that they will be able to convince somebody else to exchange the gold for resources in the future. There's barter for exchange, but for wealth storage people might trade favours. If you give me resources now, then we both understand that I'll do the same for you in the future. Literally, owing someone a favour.
There's evidence that it's a bit of a myth that primitive societies used barter, it appears that favours were more often used.
2. There's no need to exchange resources
If everyone has sufficient for their daily needs and wants, and they could requisition extra-ordinary needs then what use is gold?
I think it's the last one that you're asking about. Where money isn't a motivating factor, because it can't be exchanged for something people want.
Character motivations might be fame, games, sports, self-improvement, solitude, exploration, good deeds, service to a higher cause (God, military, civil service, their pet), or a whole bunch of things which can't easily be bought with resources.
For some people, money is useful only in that it provides a baseline standard of living, and the things that really motivate them can't be bought or sold. Put it this way - how much are you being paid to play RPGs or design campaigns for your friends?
In a world where magic or technology ensures that everyone has enough food, houses can be conjured from the air (or built by robots), there are other planets so that living space is never scarce, if you want use of a spaceship (or horse) then one can be conjured - then I suspect the motivations would be as varied as the people.
Some futurists think that technologies like fusion power and automation could transform earth's society, where money would seem as archaic as barter. A world where robots build the houses, grow the food, create luxury items.
I'd recommend Isaac Arthur's videos for inspiration. He has a whole video series on post-scarcity economies, including one on finding purpose in a post scarcity economy:
https://youtu.be/6qcggatwPBk
Now, you'll have to excuse me - it's time for me to earn some gold.