Sort by legend with highest % based on value

[Jason6]

Hi All,

I have a power bi issue i am trying to  solve and have included the sample PBI here

Requirement is to sort based on % contribution per each category 

So the example i am using is population group for each country 

In the <30 age bucket, i want sorting to be done by JP, CN and then IN ( largest to smallest )

In the 30-50 age bucket, i want the sort to be IN, CN and then JP ( largest to smallest )

In the >50 age bucket, i want sorting to be done by JP, CN and then IN ( largest to smallest )

In the below example , i can create custom rank column for each category of >30,, 30-50 and >50 and then create those separate columns as legend and enable sort by Rank

However the actual example is having multiple criteria like employement status, activity level and so on... so just basic rank system cannot be used 

So is there any way for the chart to automatically sort the country from descending to ascending order based on category

PBI file:

https://www.dropbox.com/s/l0bfkyqh2kjv8rg/Sample_PBI_Sorting.pbix?dl=0

Thank you in advance for your all your support 🙂

[darentengmfs]

Hi @Jason6 

If you want to sort by the %, highest to lowest, I believe you can click on the triple dots on the top right of your visual, and select Sort by. Then choose the %.

[Jason6]

@darentengmfs thank you for your reply

Unfortunately the sort option does not work as % is not calculated. 

I just have volume alone, even if i define % the sorting would not work

[Jason6]

@darentengmfs 

Countries would be about 10 

Categories would be 2-3

For country alone, i was able to create a reference table and then define rank for <30, 30-50, >50 based on volume in main table. Then imported the related rank to the main table from reference table.  Duplicated the country column three times and sorted each based on <30, 30-50 and >50 rank. 

So just based on country model, i am able to achieve a result

The concern is i would need to look at additonal factors like country & employment status & activity level.. This would require multiple rank based on each criteria which can be used by creating all the columns which is long tedious process. 

[darentengmfs]

@Jason6 

I made too many edits on my previous comment and it was removed.

Since you have multiple factors, this is what I would do.

1. In Power Query, duplicate your data table, remove all columns except for 1 factor, remove duplicates
2. Add Index Column to give each distinct row an ID
3. Rename this query as the Factor's title, e.g. Factor A, Factor B, and so on
4. Repeat steps 1, 2, and 3 for each additional factor you have

Once you've done this, do a lookup value for each factor you have by the tables you created in Step 1, 2, 3, and 4, with the result column as the Index column you created above.

Then,

1. Create a unique column for each Factor you have:  UID Factor A = CONCATENATE('Table'[Country],'Table'[Index Column Factor A])
2. Sort UID by POP
3. Use UID as Legend instead

Note that you will have to create these columns for each factor columns you have. So if you have Category, Activity Level, and Employment Status, you will have to do this 3 times.

[darentengmfs]

Hi @Jason6 

Please read my updated comment above.

I believe in your case of having other factors, you can actually use the suggestion I have provided. However, one thing you have to do to make the legend less tedious is to assign an ID for each legend. e.g. 1 = <30, 2 = 30 - 50, and 3 = >50. That way, your legends will look like JP1, IN1, CN1 and so on.

You have to do assign an ID for each factor columns you have, and for each factor, you have to create 1 new column as their Legend. e.g. Legend - Age, Legend - Employment Status, Legend - Activity Level. All these legends will just be sorted by the Ratio, as mentioned in the previous comment,.