Let's consider the Balista Elephant. Ivory is less common then copper, iron or horses. So, by default, you will be able to use this unit less than any other uu from comparable eras. However, let us say for the sake of argument that we are guaranteed Ivory. How does the ballista elephant compare to a regular elephant? Both can dominate swords, axes, mounted units, and archers, while pikeman give them some trouble.
When then does the unique advantage of the BE to target mounted units outside of cities come in to play? When mounted units are accompanied by pikemen. So, when an enemy unit who's primary advantage is it's mobility has a 1 move infantry babysitter 24/7 is the case when the BE comes into its own. To paraphrase the previous sentence:
The BE is useful when the AI or human opponent decides to behave like a lobotomized fish.
Mounted units are used for their mobility, keeping mounted units on the defensive in stacks with infantry (pikemen) is pointless. Mounted units are fast attackers, excellent pillagers, and all round excellent units because of their exceptional mobility and
offensive strength. Remove this mobility by tying them to pikemen, and you get a stack of expensive slow units that don't receive defensive bonuses or city raider promotions

.
The BE thrives in precisely such conditions. It thrives, compared to the regular elephant, when your opponent does not know how to use their mounted units properly. In other words, it thrives when your opponent behaves in a completely incompetent manner, when you, the player, should not need a UU to beat the incompetent adversary.
This is not tot take away from the elephant as a decent (although rare) unit. The elephant is quite good, but the ballista elephant is only an improvement on this good unit when your opponent makes such poor tactical decisions that you really shouldn't need a UU to beat them in the first place. Again, having mounted units tied 24/7 to infantry is
bad, iirc no AI is that dumb, and if the enemy mounted units aren't tied to infantry, than regular elephants will serve you just as well.