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

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.



    • lkalawski's avatar
      lkalawski
      Icon for Resident Rockstar rankResident Rockstar
      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

        lkalawski , Thank you.

         

        I have modified your original DAX a bit my adding ALLSELECTED to the _valueranking variable and it works perfectly now.

         

         

        Thank you for your help once again and have a great weekend 🙂