no you shouldn’t need to, especially on new rotors, but it’s a possibility: warping of the rotor face. if this is the problem you could take off the offending rotor (should probably check all 4 though if you’re paying careful attention you may notice the vibration comes from one corner) and examine both inner and outer faces with a good quality straightedge against a light all the way around for a definitive diagnosis. correction would involve “truing” on a brake lathe. otoh, I would expect the vibration pattern to be basically inverse of what you describe- less vibration at lower brake force and more at high.

Another thing I would check is to make double sure you A. didn’t counterthread any of your lugnuts and B. tightened them all to within the specified torque range. A loose wheel could be caused by counterthreading, under-, or over-torquing your lugnuts. Loose wheel would definitely cause worrying vibrations, but would be more likely noticeable at higher vehicle speeds or tire rpms, more-or-less independent of brakes. You probably already know all this but it bears repeating, you should always start a thread by hand and get several good turns on it before moving to power tools, and finish with a torque wrench if specified.

Looking more closely at the op, I see you say the problem is a noise however. Are you feeling the vehicle shake? Can you feel it through the pedal, or a side-to-side motion in your seat? Or is it more of a squeaking, grating, metal-on-metal noise? If this is the case, some of your brake hardware may be scraping against the rotor at light brake forces. I would carefully listen for which corner was making the noise and do a careful reinstall there.

Another possibility I saw once was a dust cover that had been stepped on (by me- oops) while I was reinstalling it. It came back with a noise complaint from that corner and the cover piece had to be bent back into shape so it was out of the way of the rotor while the front wheels were pointed.