Forum Discussion

AdamMetz23's avatar
AdamMetz23
Icon for Advocate I rankAdvocate 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.

AdamMetz23's avatar
AdamMetz23
Icon for Advocate I rankAdvocate 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
Icon for Resident Rockstar rankResident Rockstar
2 years ago

AdamMetz23 ,

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

 

  • AdamMetz23's avatar
    AdamMetz23
    Icon for Advocate I rankAdvocate 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 :).

     

     

    • lkalawski's avatar
      lkalawski
      Icon for Resident Rockstar rankResident Rockstar
      2 years ago

      AdamMetz23 ,

      The values ​​you currently have are fine because they don't take Value into account.
      In this solution we combine two sorts. Please add __ValueRanking to this matrix - it should sort based on values.
      And the sum of these two rankings creates the main sorting 🙂

      • AdamMetz23's avatar
        AdamMetz23
        Icon for Advocate I rankAdvocate I
        2 years ago

        lkalawski Thank you.


        Oh I see so the ranking is not based on the value currently but on the Group/Category.

         

        I can see that if I look at the valueranking variable however it looks strange if you consider the ranking 267:

        Ranking Test returns _ValueRanking on the print screen below:

         

        Ideally for the visualizations the rank should be like this:

         

         

        Would it be possible for you to help me modify the DAX Code to represent the print screen with the red indicated ranking structure above:

        This is the code I would like to modify to reflect the changes:

         

        Do I think correctly that the filter context needs to be changed somehow in the DAX?

        Thank you.