In my experience ORMs are great for simple CRUD but terrible once you do anything analytical in nature. Even stuff like account summaries often go sideways and it's better to handcraft.
For the CRUD side it's worth it I think, but yeah definitely a balancing act between the two.
One approach we use is to put complicated stuff in views, then use the orm to perform simple queries against those. That's especially useful for complex relationships between tables.
We do this too. Also allows for making changes purely at SQL level without having to do code changes - as long as the select clause signature is the same, the application doesn't know any better. The downside is that you have to be careful not to a build an impenetrable nesting of its-views-all-the-way-down.
We have an in-house migration management system that allows us to update views pretty safely and reliably, even with nested dependencies. Basically, on every release, all of the views are dropped and recreated, so we don't have to worry as much about that. (But still be very careful with nesting your views)
For the CRUD side it's worth it I think, but yeah definitely a balancing act between the two.