Before the interface update, Steam user profile header have this blue texture look where you could make your avatar transparent by using it's texture underneath your avatar layer.

 
Now it uses a semi-transparent header instead. And by looking at the page's code, it is proven that the header no longer uses an image texture but a HTML generated transparent color (...Is that what it's called? Please do correct me).



There are some template texture for these new interface themes indeed like this one, however it may not work properly if your profile background is too bright as shown below.

 
PNG file type avatar hasn't been supported yet, since Steam will still convert them to JPG with black background. But, allow me to offer you this ghetto way to make your avatar look transparent again!

Firstly, set the Steam background that you want to use with. And I think it's preferred to set it with the "original size" option since it'll show your profile equally to every monitor resolution, whereas the "full screen" option will stretch the background to the viewer's screen resolution and aspect ratio.

 
Open your Steam profile using a browser, then take a screenshot capturing your avatar and the header to match the avatar position later on.

 
Inspect the page element, then delete the <div class="profile_header_content"> node.

 
Take a screenshot of the header again where it should look clean like what as shown below.

Using your own preferred image editor, match up the two screenshot you've previously taken. Then crop the second screenshot according to the position where the avatar should sit.

 
Even though that it's a crime for the perfectionists, I just crop it without any exact measurement so...
But anyway, that should get you the avatar bottom layer like this one.


 
Then just slap your preferred avatar to it, mine would look like this.


 

Set it as your profile picture and that's it!

 
And of course, result may vary depending on the background and theme used.

 
I hope this helps you, cheers.