Forum Discussion

flaviosouzaab's avatar
flaviosouzaab
Regular Visitor
4 years ago
Solved

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 hi...
Need Help
Show and Tell
Tips and Tricks
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    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.

truptis's avatar
truptis
Community Champion
4 years ago

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.

flaviosouzaab's avatar
flaviosouzaab
Regular Visitor
4 years ago

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