Sort by legend with highest % based on value

Question

Hi All,I have a power bi issue i am trying to&nbsp; solve and have included the sample PBI hereRequirement is to sort based on % contribution per each category&nbsp;So the example i am using is population group for each country&nbsp;In the &lt;30 age bucket, i want sorting to be done by JP, CN and then IN&nbsp;( 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 &gt;50 age bucket, i want sorting to be done by JP, CN and then IN&nbsp;( largest to smallest )&nbsp;In the below example , i can create custom rank column for each category of &gt;30,, 30-50 and &gt;50 and then create those separate columns as legend and enable sort by RankHowever the actual example is having multiple criteria like employement status, activity level and so on... so just basic rank system cannot be used&nbsp;&nbsp;So is there any way for the chart to automatically sort the country from descending to ascending order based on category&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PBI file: https://www.dropbox.com/s/l0bfkyqh2kjv8rg/Sample_PBI_Sorting.pbix?dl=0&nbsp;&nbsp;Thank you in advance for your all your support 🙂&nbsp;

darentengmfs · Answer

Hi&nbsp;Jason6&nbsp;&nbsp;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 %.&nbsp;&nbsp;

jason6 · Answer

darentengmfs thank you for your replyUnfortunately the sort option does not work as % is not calculated.&nbsp;I just have volume alone, even if i define % the sorting would not work&nbsp;&nbsp;&nbsp;

jason6 · Answer

darentengmfs&nbsp;Countries would be about 10&nbsp;Categories would be 2-3&nbsp;For country alone, i was able to create a reference table and then define rank for &lt;30, 30-50, &gt;50 based on volume in main table. Then imported the related rank to the main table from reference table.&nbsp; Duplicated the country column three times and sorted each based on &lt;30, 30-50 and &gt;50 rank.&nbsp;So just based on country model, i am able to achieve a result&nbsp;The concern is i would need to look at additonal factors like country &amp; employment status &amp; 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.&nbsp;&nbsp;

darentengmfs · Answer

Hi&nbsp;Jason6&nbsp;&nbsp;Please read my updated comment above.&nbsp;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 = &lt;30, 2 = 30 - 50, and 3 = &gt;50. That way, your legends will look like JP1, IN1, CN1 and so on.&nbsp;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,.

darentengmfs · Answer

Jason6&nbsp;&nbsp;I made too many edits on my previous comment and it was removed.&nbsp;Since you have multiple factors, this is what I would do.&nbsp;In Power Query, duplicate your data table, remove all columns except for 1 factor, remove duplicatesAdd Index Column to give each distinct row an IDRename this query as the Factor's title, e.g. Factor A, Factor B, and so onRepeat steps 1, 2, and 3 for each additional factor you haveOnce 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.&nbsp;Then,&nbsp;Create a unique column for each Factor you have:&nbsp; UID Factor A&nbsp;=&nbsp;CONCATENATE('Table'[Country],'Table'[Index Column Factor A])Sort UID by POPUse UID as Legend insteadNote 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.&nbsp;

Forum Discussion

Sort by legend with highest % based on value

5 Replies

Related content

Find Highest Value in a Text Based Column

Filter a column with duplicate values based on the highest Index value

Highest Status

2nd Highest Value

Clustered column chart incomplete legend