Forum Discussion
Multi column order in pivot table
Hi,
I'm trying to do an apparently simple sort in a pivot table but I'm not getting it, here's the table format:
My goal is to sort the cluster column and the total column from highest to lowest, the cluster column is already sorted by a column called "order_cluster":
The correct ordering that the table should be is:
Cluster: "Vip > Gold > Silver" and in each group the ordering of customers would be from highest to lowest, but I can't sort this table by the cluster column and by the total of the pivot table at the same time, one sort cancels the other, since I tried to hold control or alt before sorting the columns but it didn't work, one sort keeps canceling the other.
How do I create this ordering in this table?
Hi flaviosouzaab ,
Try the following formula for group ranking:
Sum = SUM(Sales[Volume])Combined Rank = RANKX ( ALL ( Sales[Cluster] ), CALCULATE ( SELECTEDVALUE ( Sales[Cluster] ) ), , ASC ) + RANKX ( ALL ( Sales[Category] ), [Sum] ) / ( CALCULATE ( DISTINCTCOUNT ( Sales[Category] ), ALL ( Sales ) ) + 1 )
If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi flaviosouzaab ,
Approach 1:
Write this m code:
= Table.Sort(#"Changed type",{{"cluster_order", Order.Ascending}, {"Total", Order.Descending}})
Cluster_order is that column where in you have given your ranking - i.e. ViP>Gold>silver
Approach 2:
Create a custom sort column Where in VIP ->3, gold->2 and silver->1
And then concatenate this new column with your total column and then sort this new concatenated column in descending order
flaviosouzaab -> mark this as a solution if it helps you and hit the thumbs up. Thank you.
Thanks for the answer, I don't have a total column in my base, this total that appears in the table is the total generated by the pivot table, I can't create this M code to sort the total in the base and not concatenate columns with this total
Hi flaviosouzaab ,
Try the following formula for group ranking:
Sum = SUM(Sales[Volume])Combined Rank = RANKX ( ALL ( Sales[Cluster] ), CALCULATE ( SELECTEDVALUE ( Sales[Cluster] ) ), , ASC ) + RANKX ( ALL ( Sales[Category] ), [Sum] ) / ( CALCULATE ( DISTINCTCOUNT ( Sales[Category] ), ALL ( Sales ) ) + 1 )
If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
