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.

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 🙂