Sorting column chart X axis by two level hierarchies

[AdamMetz23]

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 (32,29,29,36) should be sorted in descending order. However the trick is that it should consider the region column values (EAPRO, ECARO). It means that the measure value should be 32 for Myanmar, 29 for Papaue New Guinea in EAPRO but after when it comes to ECARO Region the descending order sort should start again and Ukraine should be 36 and then Turkiye should be 29. So the descending order logic should restart for each region.

Currently If I try to sort by the measure value descending, it disregards the region hiearchy:

The region values are not batched into one but only the measure value matters in the sorting.

Do you know a way how to create this hiearchy custom sorting by any chance?

Thank you a lot for your help and have a great day,

Adam

[lkalawski]

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]

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]

@AdamMetz23 ,

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

[AdamMetz23]

@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]

@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]

@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 · Accepted solution]

@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]

@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 🙂

[amitchandak]

@AdamMetz23 ,  if you use legend then you have an option to sort by value, within axis sorting . Check if that can help