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

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! 😁