Forum Discussion

AdamMetz23's avatar
AdamMetz23
Advocate I
2 years ago
Solved

Sorting column chart X axis by two level hierarchies

Dear Power BI Community.

 

Can you please help me with  the following issue if you have seen similar situation before?

Situation snippet: Here are values on a column chart. the measure values (32,29,29,36) should be sorted in descending order. However the trick is that it should consider the region column values (EAPRO, ECARO). It means that the measure value should be 32 for Myanmar, 29 for Papaue New Guinea in EAPRO but after when it comes to ECARO Region the descending order sort should start again and Ukraine should be 36 and then Turkiye should be 29. So the descending order logic should restart for each region.

 

 

Currently If I try to sort by the measure value descending, it disregards the region hiearchy:


 

The region values are not batched into one but only the measure value matters in the sorting.

 

Do you know a way how to create this hiearchy custom sorting by any chance?

Thank you a lot for your help and have a great day,

Adam

 

Need Help
  • lkalawski's avatar
    lkalawski
    2 years ago

    AdamMetz23 ,

    It's sometimes hard to find the problem without knowing the data model, but try using this code for a second sort:

    RANK (
    SKIP,
    ALLSELECTED ( 'Table' ),
    ORDERBY ( 'Table'[Value], DESC, 'Table'[Group], ASC ),
    LAST,
    PARTITIONBY ( 'Table'[Group] )
)

    This code does ranking based on values ​​and partitions by groups.

9 Replies

  • lkalawski's avatar
    lkalawski
    Resident Rockstar
    2 years ago

    Hi AdamMetz23 ,

     

    To sort values ​​within a group, you need to create an additional measure, which is best added as a tooltip.
    This measure will sort the values ​​taking into account both the group and the values ​​that are in it.

    1. Create measure:

    Ranking =
VAR __CurrentGroup =
    SELECTEDVALUE ( 'Table'[Group] )
VAR __GroupRanking =
    COUNTROWS (
        FILTER ( ALL ( 'Table'[Group] ), 'Table'[Group] <= __CurrentGroup )
    )
VAR __ValueRanking =
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[Group] = MAX ( 'Table'[Group] ) ),
        CALCULATE ( SUM ( 'Table'[Value] ) ),
        ,
        DESC
    )
VAR __Result =
    __GroupRanking + DIVIDE ( __ValueRanking, 1000 )
RETURN
    __Result

    This measure, as I wrote above, first sorts all groups alphabetically and then checks the sorting by nominal value. We then add the sum of these and get a result that takes both factors into account.

     

    2. Add a measure as a tooltip to the visualization.

    3. Set sorting using Ranking

    The result:

    If you have any further questions, feel free to ask.

     

    If I helped, accept the post as a solution and give a kudos. 👍 Thanks! 😁

    • AdamMetz23's avatar
      AdamMetz23
      Advocate I
      2 years ago

      Hi Ikalawski,

       

      Thank you for your great help. I did what you suggested, it does not seem to work yet.

       

       

       

       

      Do you think I missed something?

      Both columns the Country and Region comes from the same table and the measure is just a relatively easy CALCULATE on the same table to distinctcount the values for the countries and region:

       

       

       

      I would be grateful if you could help but thank you already for your effort.

      • lkalawski's avatar
        lkalawski
        Resident Rockstar
        2 years ago

        AdamMetz23 ,

        Please check what __GroupRanking returns and attach such a matrix for verification.

         

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    AdamMetz23 ,  if you use legend then you have an option to sort by value, within axis sorting . Check if that can help