Visual Effect displayed in front of all other objects with transparency regardless of priority

Morning.
You should be able to properly order your translucent materials without touching the Sorting Priority.
As you are using HDRP, I recommend you look at this page, which explains the Renderer and Material priority in detail.

With the default settings (Mesh Renderer-> Priority = 0 && Material > Sorting priority =0 ) and your VFX and HDRP/lit Sphere both using Translucent shader, the Renderer should use the distance to the Camera .

You have to be aware that it’s from the Bounding Box center to the Camera that is used. This means that you can have sorting issues if your Mesh Bounding Box or VFXGraph bounding box isn’t properly set up.

9831474--1414014--Unity_HczbuPgmE6.gif
In this simple example, my particles are using the default settings and an additive blend mode while the Sphere is using the HDRP/Lit Translucent Shader also with default settings. As you can see, the ordering is correct when the bounding box of my VFX is properly set up. As soon as I increase the Bounding box so that the Bounding Box Center is closer to the camera than the Sphere, some ordering issues happen with the Particles being drawn on top.

So, I would first suggest that you check the bound of your Sphere and your VFX Graph.

In this second example, I’m using only two GameObjects: a regular sphere and a custom mesh composed of two spheres apart from each other. As you can see the Bounds of my CustomMesh is encapsulating the two spheres.

As those translucent spheres don’t write to depth, the Bounding Box center is used for the distance to the camera, which can lead to issues in ordering with the red Sphere.
9831474--1414035--Unity_KQKtHM6Zs4.gif

So, first, get back to default settings. Don’t use the Sorting Layer, as it’s used for 2D Rendering, and you won’t find this option in HDRP for regular MeshRenderer. From here, check the bounds of your VFXGraph waterfall and tell me if this resolves your issue.

You can look at this post if you want more information relative to Per-particles and VFX Output sorting.
Hope this helps. Wish you a sunny day