The Avatar can be very effective when properly supported, sent after the right targets utilizing a cautious approach to movement and cover.
In the current state of the game he cannot be sent stomping up the board aggressively in the beginning phases of the game. He needs to be held back cautiously moving up using as much LOS blocking terrain as possible. He's gotta used as a backfield defender, who by turns 3-5 is ready to engage mid-field infantry units. Ideal targets are Necron Warriors, mobs of Ork boys, Tactical Marines and other big units of standard infantry.
He excels at clearing mid-field objectives, tying up and quickly dispatching dangerous shooting units, or other units which could tarpit a units like Wraithknights or units which could grab your own high value shooting units like Laser Bikes.
With his buffing abilities he makes units like Striking Scorpions, Howling Banshees, and Wraithblades very nice. Yet they too must be held back, waiting to engage mid-field targets. Wraithlords and Wraithknights are excellent partners for the Avatar and alongside them you can truly dominate the center of the board.
With a strong, streamlined list with a nice attack wing of mobile dangerous shooting like Laser-Bikes, Dire Avengers in Wave Serpents and a Wraithknight, the Avatar and a single partnering close-combat unit (Wraithblades, Wraithlord, Banshees, Scorpions) can make for an incredibley potent list with great balance. I think such an approach stacks up very well against Space Marines, Necrons and Orks.
Again he's a trailer unit, that lurks slowly up to mid-field behind as much LOS terrain as possible, preferably within assistance range from a Wraithknight or Wraithlord. Under those conditions he can be very effective.