this probably is more about specific implementations than pivot table in general, but I often find it's "lacking" when wanting to do some more advanced things with it.

To name a few:

The way how the "values" are generated is very limited in both Excel and Google Sheets (in different ways).

The way how filter/sorting works with pivot table isn't the most straightforward or flexible.

the "UI" elements (headers, styles, etc.) is very hard to control. I often find myself creating a pivot table, and copy it somewhere else, and manually fix bunch of stuff -- which kinda defeats the point of it (since I can no longer dynamically update it).

Disclaimer: I'm by no means a spreadsheet expert so I may just miss something.