Are you sure it's not a water problem? The location next to the door leads me to this conclusion. An improperly flashed door will leak more than likely and result in exactly what you've described.

Can you get at the subfloor from below? If you can, get down there and poke around...literally. If it is water, you should see darkened wood or white-ish stains. If you poke at it with a screwdriver if might feel "punky".

Hopefully, it's not a water problem. If it is and you simply fix a weak joist, but not the root cause, you'll be right back in the same situation 5 yrs from now.

You need to solidify the floor and get rid of the flex. How that is done would depend on what is there now. Might mean tearing out the floor and installing cement backer boards.

The problem has one solution. Remove the tile and probably water damaged, improperly sized or improperly secured or the wrong substrate all together.
Agree with improper flashing causing the problem. I just tore out a tile foyer for a customer, due to a few of these problems. Previous owner nailed down 1/4" wafer board over maple strip flooring and installed tile on top. Water wicked in from the homeowner(again previous) installed front door. No flashing, wet sand against the wood and no caulking under the threshold.
Ron