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 (3...
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.

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.

       

      • AdamMetz23's avatar
        AdamMetz23
        Advocate I
        2 years ago

        lkalawski , thank you that is a great validation check idea.

         

        I see the problem:

         

         

        Even if Turkiye sum of value is 29 while Ukraine is 36, when it  comes to ranking both values are ranked as 2 etc. for the others.

         

        Do you know by any chance where I made a mistake in the DAX? Every column (Group, Category) and value (Sum of Value measure) is in the same table so I think the issue is with the filter context somehow?

        What do you think? 

        Thank you a lot if you had time to check it but I understand if you have other things to do as well :).