Cannot sort series by value in Pivot Tables when using metrics as rows

[jamzl]

Bug description

When using metrics as rows in pivot table, sorting is done by row at the most granular level, not by series.
E.g. when sorting by Key A-Z, metrics are displayed correctly under their parent in the series.

However, when sorting by Value, sorting is done by row, not by series (e.g. "Puzzle" genre is now split into 2 separated groups)

I've spent a lot of time searching for solutions for this, nothing came up. I tried sorting the raw data in the way that I want, but the sorting is overridden by the pivot.

How to reproduce the bug

1. Create Pivot Table
2. Select >1 metric
3. Select metrics as rows
4. Go to "Customize" and choose sort by values

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.