PIVOTBY ROW_SORT_ORDER not working as expected/desired

Question

I've reduced a much more complex real-world problem to this simple testcase. My objective is to sort by most recent year SUM ascending (most negative to most positive):

Thinking I understand the docs

I count the 2005 column as column 3, but here's what happens:

Hmm. Let's see what happens if I try by column 2 (the 2004 sum values):

The first one may be accidentally right, but the second one is clearly wrong and no different. So, let's see if we can invert the "cat" sort to descend:

Finally,_ROW_SORT_ORDER works as I expected. Too bad that's not what I needed to do.

I must be missing something painfully obvious here, but ROW_SORT_ORDER does not seem to work the way I need it to. Is there another way? Or what is it I'm missing?

FWIW, this is what Excel I'm running on Win11:

Thanks in advance for any help on this!

Answer 1

Hi. "If" you included the totals, then that becomes the second. You can sort desending on this "second" column.

I might wrap this in a sort function.

> ... My objective is to sort by most recent year SUM ascending (most negative to most positive):

So, adding sort would give you:

Answer 2

Do I follow right that you are saying the only two columns that can be used to set ROW_SORT_ORDER in PIVOTBY are the "rows" column itself (ROW_SORT_ORDER 1/-1) and the optional total column (ROW_SORT_ORDER 2/-2)? So, no PIVOTBY equivalent to the ability in classic Pivot Tables to sort all table rows on one of the aggregated columns? Ouch.

Could you share your SORT formula? Getting to the same place, I did this gyration to VSTACK the header row back on top of the sorted pivot rows (ugly though it is):

=LET(pvt,PIVOTBY(Table1[cat],Table1[yr],Table1[amt],SUM,0,0,,0),VSTACK(TAKE(pvt,1),SORT(DROP(pvt,1),3,1)))

Answer 3

For anybody who winds up in this thread for similar reasons, here's some insight that might be helpful:

Answer 4

Hi. Yes, that is basically the way I understand it.