With a budget of $120 million, Francis Ford Coppola's "Megalopolis" grossed only $4 million on its opening weekend, making it one of the worst box office openings for a $100M+ movie to date.
Factoring in marketing costs and the theaters taking their cut of the profits, Megalopolis would need to make at least $300 million to break even. I think it's safe to say that's not happening.
It would have been THE worst opening for a $100 million movie ever, had it not been for Pluto Nash's horriffic opening 22 years ago.
I think for someone like that, it isn't about the money, it's about making your artistic vision happen, using the clout you built elsewhere to push through a project that was never financially viable but it's your dream as a filmmaker.
Sometimes those stories end up becoming some of the biggest movies of all time, but often they just end up being a big waste of money except for the guy who gots to make his dream movie.
The Thief and the Cobbler is one, it was massively expensive and destroyed the studio, but it was the animator's magnum opus he worked on for 30 years.
Showgirls was one of the movies Paul Verhoven pushed for as a personal project, and it literally destroyed the careers of some of the people who were part of the project (and gave many of us a chance to see tits on basic cable at 15, so your sacrifice will not be forgotten)
Final Fantasy: The Spirits Within was a first of its kind, a photorealistic movie, but the cost and the fact that the movie just wasn't very good basically destroyed the studio after one picture.
Disney's Treasure Planet was intended to be the magnum opus of its creators, but ended up being a nail in the coffin of disney animated movies.
Cameron's Avatar is an example in the other direction, where it was this weird movie about blue aliens he really wanted to make that ended up making all of the money. His movie Titanic is another weird one, where you have a 3 and a half hour historical romance that became the top movie on earth.
Christopher Nolan's Inception was also mind bendingly popular, and one of the films he used his clout to create.
I also heard about a movie from 1980 called Heaven's Gate which destroyed the director, the studio, and essentially ended the era of director-led movies because studios were too gun-shy after that bomb to let that happen again.
So as you can see, these sort of risky auteur films can either be the biggest flops or the biggest home runs, it really depends on the film and the world around it in that moment.