Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
iGi
Advocate I
Advocate I

Custom Sorting in PowerBI?

Hello all, 

 

Seeing as how helpful people were with my last two questions, I thought I'd ask another, which has been nagging me. 

 

I'm aware of the sorting function in graphs, but that seems to sort the items alphabetically. 

 

What I'm hoping to do is sort non-alphabetically, in an order that makes sense otherwise - highest to lowest, or whatever the case. 

See here: 

PowerBI Sorting Question.png

 

I'd like it to read Critical, High, Medium, Low. Is there anything I can do here? 

 

Thank you!

1 ACCEPTED SOLUTION
v-haibl-msft
Microsoft Employee
Microsoft Employee

@iGi

 

You can create a table like following one and create relationship with the original table.

Custom Sorting in PowerBI_1.jpg

 

Then create a calculated column in original table with following formula.

Column = RELATED( Table2[ID] )

Custom Sorting in PowerBI_2.jpg

 

At last, select the Type column and make it sorted by above created column.

Custom Sorting in PowerBI_3.jpg

 

Custom Sorting in PowerBI_4.jpg

 

Best Regards,

Herbert

View solution in original post

27 REPLIES 27
Anonymous
Not applicable

Hi Everyone,

 

This option seems to be completely not working when i tried the same procedure and sorted the stacked column chart my chart started showing all as 100% for all items.

 

Suggest .

 

Thanks,

Abhilash

Anonymous
Not applicable

For clear view please refer to the below images Before Sorting and After Sorting why the values are changing to 100% for each category.

 

Please note i have exactly followed the same procedure as mentioned in the solution.

 

 

Before SortingBefore Sorting

 

After SortingAfter Sorting

 

Hi there,

It seems like when calculating percentage for each category after sorting, you will have to make a slight change to your formula to also aggregate based on the new column created in original table.

for example:

theblackknight_0-1729247568533.png

Here earlier I was only aggregating on 

'AggregateGradingData'[vw_REPGradingApprenticeFinalGrade.AM_Grade] in all because of which all of the grades were showing 100%, after I made change to include thew new calculated column as well, it started working like magic.

I hope that helps.

here are screenshots of changes before and after: mark that am grade pos is calulated column using related:
theblackknight_1-1729247715117.png

resultant visual:

theblackknight_2-1729247736093.png


after change in formula:

theblackknight_3-1729247771438.png


resultant visual:

theblackknight_4-1729247790749.png

 



kunalmishra07
New Member

Hi All, 

Thank you for sharing the solution. 

 

1. By using reference table

2. Conditional Column

 

I was wondering what is the difference between doing it by using Conditional column and doing the same with calculated column. 

 

Steps Followed :-

1. Go to Data Tab

2. Go to Modelling ribon and select New column

3. Create column using reference column according to what we need to sort. 

 

But sorting is not happening and i am getting error that Reference column could not be sort by Calculated column.

 

Request you to throw light on the same.

brjones
Advocate I
Advocate I

 

Thanks so much for this!  

 

I had to delete some visualizations and re-create them in order for it to work correctly, but it's working beautifully now 🙂

 

GREAT TIP!

Same for me. I had to delete my chart and create again.

 

Thanks for the tip. A good work around. 

Re-creating visualization worked for me. Thank you

im having same problem, its not sorted out.. do i  need to redo the visualisation?

Hi guys

 

This solution worked great for a situation where I had months - August, September and October not displaying in the correct order in my visual.

 

However, I was wondering, why did my creating of a calculated column in the SAME table using the following formula not work? So within the same table, I created a calculated column:

COLUMNSORT = IF('Table1'[MONTH] = "August", 1, IF('Table1'[MONTH] = "September", 2, IF('Table'[MONTH] = "October", 3)))

 

And then I tried to sort MONTH using this new calculated column 'COLUMNSORT', but it displays a message 'This column cannot be sorted by a column that is directly or indirectly sorted by this column'.

 

Why does it display this error message? I understand the logic that it cant sort the column because this calculated column directly sorts MONTH. So then why can it do a sort off a RELATED function which brings the exact same values into the main table? Isn't it the same thing basically?

 

Just trying to understand the logic in the two methods.

 

Thanks all

 

Regards

Ahxl

christ7
Frequent Visitor

How did everyone put the ID in the second column in the newly created table?

What formula is it?

Anonymous
Not applicable

Hi,

 

Although I have used my sorted column as default column to be used as a sort, Im not able to see my bar chart change the sort automatically.

djk1000
Frequent Visitor

@ahxl

 

You can do essentially the same thing, creating your COLUMNSORT column, but do it in the Query Editor with "Add Column", then save and sort by the new column back out  in the PowerBI intrerface. It will get rid of the circular dependency.

brjones
Advocate I
Advocate I

I thought this would solve my problem, but for some reason when I do the 'sort by column' step it does not sort by that column.  I made sure that no other columns had any kind of sort applied to them.

I created a new table based on an excel file.  

Then created a link between the new table and the one I wanted to sort by category.

Then did the sort by column step.  The screen flashed like it was doing something, but nothing happened.

 

The Table1 (T1) in my case is a query built from some other queries.

My Table2 (T2) is an excel file with two columns that I imported.

The linked column is labeled 'Reason Lost', with a 'Many to One' setting from T1 to T2, and cross filter set to 'both'

 

I would love some help with this, because the only other way I know to sort these correctly is to add an index in front of every item to get it to sort correctly, and that is visually distracting.

v-haibl-msft
Microsoft Employee
Microsoft Employee

@iGi

 

You can create a table like following one and create relationship with the original table.

Custom Sorting in PowerBI_1.jpg

 

Then create a calculated column in original table with following formula.

Column = RELATED( Table2[ID] )

Custom Sorting in PowerBI_2.jpg

 

At last, select the Type column and make it sorted by above created column.

Custom Sorting in PowerBI_3.jpg

 

Custom Sorting in PowerBI_4.jpg

 

Best Regards,

Herbert

I have similare question but with year and type column:- 

for Year 2015 i want to see type order(Critical, High,Medium, Low) but for 2016 i want to see type order(High,Critical,Medium, Low) not in graph maybe in slicer/metric visual - IS IT POSSIBLE? I have tried above solution but doesn't work with two column, tried related formula, sort by column nothing works. works fine for 2015 not for 2016. Please help..

 

HKInd_0-1687884690467.png

 

Is there a way to do this if I'm pulling from a live dataset? I can't add columns or adjust the data. For example see my attachment.

fhabbiyy_0-1677776617646.png

 

I have 5 columns that need to be reordered in different ways in my fact table.  I created 5 seperate demention tables with the column and a new column that attched a resort to them.  then I sorted on the that new column.  worked great and the data reorganized at the data level in the demention table.  however,  when I use that resorted field from the demention table the data does not reorganize.... please please help im so stuck. 

 

I can not reorder my main table as there are 5 different fields that need to be reordered for different visuals. 

22LACMT_0-1671646483739.png

 

 

Hello @v-haibl-msft 

I have tried that, but the problem I am facing is that I have a category that has no value in our original table. It is not appearing in my graph but I want to show it in my chart. What to do?

Anonymous
Not applicable

This is perfectly worked. thanks you.

Anonymous
Not applicable

Awesome tip!

I was struggling to reorder certain data, but this worked like a charm!

 

Thanks a lot!!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.