Custom Sort Order Trick in Power BI & Power Pivot

[@djaurit]

I completely agree with what many others have shared... your videos are very concise and tremendously helpful, no wasting time, which I greatly appreciate... THANK YOU!

[@MyOnlineTrainingHub]

🙏 So nice of you, Dan!

[@Picla_Peremohy]

I cannot believe the number of "other" videos I went through where their solution just did not work. My Christmas present this year was finding your excellent and so well explained tutorial on how to make this work. I especially appreciated the tip about not just adding a sort column to the table and instead use a relationship. Your solution worked right out of the box and I must remember to always come to your channel first for the proper education. Thank you so very much for putting this together.

[@MyOnlineTrainingHub]

Wonderful to hear! Merry Christmas 🎅😊

[@jamescrocker8341]

Thank you! Solved a problem I've not been able to figure out for months.

[@MyOnlineTrainingHub]

So pleased to hear that, James!

[@joannfernandez6465]

Very understandable and highly recommended. My fav youtube teacher ❤

[@MyOnlineTrainingHub]

Wow, thank you!

[@olumayowatalabi2625]

Thanks Mynda. I really like your videos a lot. Very clear, concise and detailed. Thanks a lot for sharing

[@MyOnlineTrainingHub]

Glad you like them!

[@P0rknvinZ]

Your guides were so clear and straightforward. This is soooo helpful! Thank you so much!!!

[@MyOnlineTrainingHub]

You're so welcome!

[@deldridg]

Great vid and thank you. If I may, one tiny point. At 5:02, it is often useful to summarise all years by month if you're interested in seasonal trends, as an example. No doubt your comment was contextual but I thought it worth a mention. Many thanks for this excellent tut and cheers from Sydney - Dave

[@MyOnlineTrainingHub]

Cheers, Dave!

[@xolanijozi8375]

Straight to the point. This video helped me a lot. Thank you Mynda.

[@MyOnlineTrainingHub]

Awesome to hear!

[@IvanCortinas_ES]

Good tutorial Mynda!! Creating a Calendar Table can also help solve the second case. Thanks for these Power BI tips.

[@MyOnlineTrainingHub]

Thanks, Ivan. Even with a calendar table, you still need to set the sort order for month names where their data type is text.

[@ricardostorck]

Amazing tricks! Clear and straightforward. Thank you.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@vijayarjunwadkar]

Nice tricks Mynda! I was aware of first technique, but liked the concept of using a Dimension table which is useful for handling large data. Thank you so much for sharing! 😊👍

[@MyOnlineTrainingHub]

Awesome! Thank you, Vijay!

[@StephenFiora]

Thanks queen, I can't believe I had to look at maybe 10 different websites and videos just to figure out there's an easy "sort by" button.

[@MyOnlineTrainingHub]

So glad I could help! 🙏

[@ricardogutierrezdiez9170]

Thanks Mynda, I love so much your videos, and they are very useful to improve our PBI skills. It would be great if you can show us a new video with a gateway configuration to try to update automatically the data from the source. Thanks again!!

[@MyOnlineTrainingHub]

Great to hear, Ricardo! I'll consider a video on gateways in the future, but no promises as to when. I do cover them in my Power BI course if you're interested: www.myonlinetraininghub.com/power-bi-course

[@yiubili1]

Thanks for saving my day.... how ironic that it look so many steps yo do a proper sorting here...

[@MyOnlineTrainingHub]

Glad it helped 😊

[@RobbPGL]

it solved my problem in 1 minute. Perfect :)

[@MyOnlineTrainingHub]

Awesome to hear 😊

[@diogotred]

Thank god for your life, your help was essential for what I was hoping to do!

[@MyOnlineTrainingHub]

So glad I could help!

[@henryweston8273]

Thanks, this actually also helped sort weekdays that were stored as characters

[@MyOnlineTrainingHub]

Glad it was helpful 😊

[@gopinath2479]

Excellent, I was able to fix my problem on my Dashboard.

[@MyOnlineTrainingHub]

Wonderful to hear!

[@sebastianfrancoloyolaperez5285]

Thank you very much for this trick! It did the job. Thank you again.

[@MyOnlineTrainingHub]

My pleasure 😊

[@aabdifatahhas]

Thanks you solved a problem excellent work #Mynda iron 👏 women ❤

[@MyOnlineTrainingHub]

So glad I could help!

[@khyambaig7121]

Fantastic video, as always! Thank you, Mynda :)

[@MyOnlineTrainingHub]

Thanks for watching!

[@dr.unmeshedwankar2005]

Amazing video clarified my doubts

[@MyOnlineTrainingHub]

Glad I could help 😊

[@mohammedal-sabbagh8463]

Thanks a lot, I faced the same and solved it now.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@paulussantosociwidjaja4781]

Thank you for the learning, cool approach.

[@MyOnlineTrainingHub]

My pleasure!

[@dom2114]

So helpful! Thanks for putting this content out here

[@MyOnlineTrainingHub]

My pleasure!

[@RajendraPatel.]

Hi Mynda, you explained it like expert. Happy to learn more.. Raj fron Delhi. 😊

[@MyOnlineTrainingHub]

Thank you so much 🙂

[@ianwoodland7917]

that was excellent - that answered a problem - thanks

[@MyOnlineTrainingHub]

Great to hear!

[@jackarmes3736]

Fantastic, so helpful. Thank you!

[@MyOnlineTrainingHub]

So glad!

[@felipe4181]

Quick and amazing tip!!! Thanks Mynda!!!

[@MyOnlineTrainingHub]

Gad you liked it, Felipe!

[@AslanGilad]

is there a way to sort the legend of a stacked column visual top five when i have duplicate names in the column? the table is an origin destination matrix but its set in tow columns for slicing

[@riccardoczech6195]

As usual, you saved my life 💕

[@MyOnlineTrainingHub]

I'm so glad I could help!

[@PatGilliland]

The dimension table worked well, but I found that if the "Achievement" column contains numeric rather than text data, it doesn't work

[@MyOnlineTrainingHub]

It's probably an easy fix. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@leorc564]

Excelentes tutoriales, always nice content and power advice, thank you Mynda!

[@MyOnlineTrainingHub]

My pleasure 😊

[@leorc564]

I'm going to apply for months, thank you

[@ashitadutta8906]

What if I have multiple conditions, how can I apply in conditional column?

[@pinakd6007]

thank you. I was using create a new table option to sort it. this is helpful

[@MyOnlineTrainingHub]

Great to hear!

[@AyushYadav-mw8pd]

Thank you very much you shaved my job

[@MyOnlineTrainingHub]

Glad I could help!

[@AhmedFathy-gu4yu]

Hi Please how can I change the scale or dimensions of x axis in clustered barchart? In my clustered barchart the values of x axis are shown: 0% - 20% - 40%-60%... upto 100% I need to show the values like: 0% - 10%- 20%-30%... upto 100% Thanks in advance

[@Juan-Hdez]

Very useful. Thank you!

[@MyOnlineTrainingHub]

Glad to hear that!

[@realpulsecoin]

Love the content and the edit of the video. Vert nice when you speed up repetitive tasks 👌👏 Keeps the focus on the task. Greetings Norway

[@MyOnlineTrainingHub]

Thanks so much, Rasmus :-)

[@adhiandrianto2521]

It works for me. Thanks a lot.

[@MyOnlineTrainingHub]

Glad to hear that!

[@atuljha7155]

Hi Minda, I'm trying to create a customer sort with the help of dimension table, it worked for number and text perfectly. What turns it down is when using a special charter as a parameter to sort in order, for instance when I'm trying to create a column "Day" which ranges from -12 and +12 days from Presales (Day) it's not sorting as I expect it to be, though I've defined relationship with the base table and sort this table with "Order", it's unable to sort it. Any help in this regards will be appreciated! Regards, Atul

[@autumnyoung3927]

What about when you need to take millions of records and sort by three fields like the custom sort option in excel, how would you recommend that, it is basically like the category where there are three hundred categories, another category has 2400, and then the other is 0 to 300?The fields are text, number, number, but the text is not words. Thank you Any suggestions are appreciated.

[@wayneedmondson1065]

Awesome Mynda! Super useful tricks. Thanks for sharing :)) Thumbs up!!

[@MyOnlineTrainingHub]

Glad you liked it!!

[@andreabond6504]

I have tried this but it still shows number 10 in my sort by column as a 1! How do I get around this?

[@MyOnlineTrainingHub]

Sounds like your sort by column data type is set to text instead of a number 🤔

[@sidharthserjy8984]

What if I want to use one Sort Table for multiple other tables in Power BI. I also need the visuals created from all these tables to be interactive as well! Do I need to make a Sort Table for each or is there a work around?

[@MyOnlineTrainingHub]

As long as there is a relationship between the Sort Table and the fact table, you can use the field from the Sort Table in your visuals that has the sort rule attached to it.

[@anupajayakody458]

Hi I have a chart with the customer as the 1st level, and monthly breakdown as the next level in the drill down. I have sorted the customer(1st level) by sales value, next level I want to sort by the month number. How can I do this??

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@kingsfordgokah6240]

Just what I needed, thanks

[@MyOnlineTrainingHub]

Glad I could help!

[@YouExcelTutorials]

Thanks for another useful tutorial Mynda 👍

[@MyOnlineTrainingHub]

My pleasure!

[@madhusmitadas9919]

Thanks for sharing however this sort always resets to default when i click on reset filters.

[@MyOnlineTrainingHub]

It shouldn't do with Power Pivot. 🤔

[@lorainisrael]

Thank you!

[@MyOnlineTrainingHub]

You're welcome!

[@linguineshun8829]

So helpful, thank you!!!

[@MyOnlineTrainingHub]

You're so welcome!

[@iver1444]

Is there any way to apply a custom sort order to standalone pivot charts in Excel? By "standalone" I mean sourced from Data Model and not having a related PowerPivot table available.

[@MyOnlineTrainingHub]

The data model is Power Pivot. They are just different names for the same thing.

[@iver1444]

​@@MyOnlineTrainingHub The main question remains - is there any way to apply custom sort in Excel data model-based pivot chart? What I meant is that inserting a data model-based Pivot Chart can be done two ways: by either clicking Insert-> "Pvt Chart" or Insert -> "Pvt Chart & Pvt Table". The latter creates also a pvt table which allows to create at least a dummy 'custom sort' by draging and dropping items in the table - chart will follow changes. However how to do it in the former case?

[@darrylmorgan]

Hi Mynda!Really Helpful Tips...Thank You :)

[@MyOnlineTrainingHub]

Glad you think so, Darryl :-)

[@siyakholwakhiphi1556]

Please assist I tried to sort using this method but its not working on my data.

[@MyOnlineTrainingHub]

I suspect you haven’t used the correct field in the PivotTable. You need to use the field that has the sort field assigned to it.

[@srimathi2408]

How do we do the same with excel, power query editor? Kindly help! Thanks many in advance!

[@MyOnlineTrainingHub]

I'm not sure why you'd want a custom sort order inside Power Query, but you could add an index column sorted to your liking. Note that this won't carry over to Power Pivot or Power BI for the field you want to sort.

[@Muhzyy17]

How can I sort a Stacked Column to show the largest category at the bottom leading up to the smallest category at the top? I am aware that I can do this for 1 specific sort order by making a custom table and then creating a relationship to my main data table. In my scenario, I have a stacked column chart broken into 5 categories, by year. Where I run into a problem is that I have this same setup but differentiated on a new slide for different regions. The overall worldwide sort order is different than those of the regions. I end up with one slide that looks right and then the other 4 (the region granular slides) being out of order. I know it can be difficult to explain issues and hope for a solution over text but I hope there is enough here to understand the issue. Would love to chat as I can't find this information anywhere on the internet. Starting to think I need a custom table written in DAX to get around this issue... not sure. I'm surprised Power BI doesn't have a simple sort by Z->A within the table based upon stacked values.

[@MyOnlineTrainingHub]

Very difficult to follow the issue without seeing an example. Please post your question and sample Power BI file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@zakincc]

You are a magician ❤️

[@MyOnlineTrainingHub]

Thanks for your kind words!

[@visalatchy_naidu]

thanks a lot! this is so useful for my work. =')

[@MyOnlineTrainingHub]

Great to hear 🙏

[@bahaashehab3626]

Hi there, thank you for the technique. Although it worked on my slicer and 100% stacked bar chart, it strangely didn't work on my normal horizontal stacked bar chart. It still presented it from high to low. Any ideas as to what might be the problem?

[@MyOnlineTrainingHub]

Bar charts are sorted in reverse order to other charts. Check the box to display categories in reverse order in the chart formatting pane.

[@bahaashehab3626]

@@MyOnlineTrainingHub thank you so much it worked.🥰

[@JitendraSingh-pg9sc]

Hello Mynda, I am looking for some help. I am using date range slicer and on every selection I want to filter my page (filter chart and table data) for the selected date range and it should also include data for blank date values as well every time.

[@MyOnlineTrainingHub]

Please see this video: kzbin.info/www/bejne/p4CnipWwnL-qq9U

[@martinrodriguez469]

Thanks !!

[@MyOnlineTrainingHub]

You're welcome!

[@sunilvarma9547]

hi, if I need to sort based on latest 12 months and want only months name in the X axis, how to achieve that.for example if I take a column chart with latest 12 months relative date filter and if today is May 22, my X axis should show from June 2021 to May 2022.But want to show on X axis with just month name instead of month and year.(Jun,July,....Jan,Feb,Mar,April,May)..Please suggest

[@MyOnlineTrainingHub]

You'd need to put the year filter in the filters area of the PivotTable, i.e. not in the row or column labels. Only the month field should be in the row/column labels, that way it displays on the axis.

[@sunilvarma9547]

@@MyOnlineTrainingHub thank you so much for your reply, but with this I will not get the X axis months ordered from June to May.It always shows months only from Jan to Dec or Dec to jan.

[@MyOnlineTrainingHub]

​@@sunilvarma9547 create a Financial Year column that classifies each month into the relevant year. Use this in your filter field and then make sure your month names have the sort by set to 1 for June, 2 for May and so on.

[@chrism9037]

Neat trick Mynda!

[@MyOnlineTrainingHub]

Glad you like it, Chris :-)

[@taylorhernandez3038]

How do you create the second table that has the month and sort order? Is that a summarized table?

[@MyOnlineTrainingHub]

There are lots of ways to do this. e.g. you can create the table in Excel and bring it into Power BI with Power Query or Copy and Paste it in. Or if you already have the month names/dates in your model, you can use Power Query to create a new table from a unique list of the dates, or from a range of dates. It all depends on your model, but I hope that gives you some ideas.

[@taylorhernandez3038]

thank you! I figured out how to create a table in power query. Awesome video!

[@shoppersdream]

Thanks! Where is the file which you are sharing at your video time 3:29? I didn't find that on your link. Thanks

[@MyOnlineTrainingHub]

It's under the heading "Download this Example Power BI Desktop File" on the paged linked to in the video description. You may need to enter your email address to reveal the download link.

[@jamieward8009]

Turns out you cannot do this to a table that's been set up via DAX, because for some reason you can only see imported tables in Power Query. You can add columns, but I cannot work out how to manual add in the sort numbers, it just doesn't allow it. My issue is, I've set up a bridge table for regions using DAX, so I could have a slicer to filter between North / Midlands / South. However, I can only have them appearing in alphabetical order A-Z or Z-A. Does anybody know a way I can add a sort column into such a table or do I need to create the table in excel, import in and redo all the region relationships with the new table? P.S. I've never known a program as bad as this before, in terms of overcomplicating the simplest of things. This is happening with almost everything and it's so tiring!

[@MyOnlineTrainingHub]

You’d have to edit the DAX that created the table to include the sort order but I’m a bit confused because you mentioned power query. If it was created in power query then you can add the sort column there.

[@jamieward8009]

​@@MyOnlineTrainingHub Hi, Thanks for the reply. Good video by the way. I ended up recreating them in excel and importing back in. I may be mixed up with the names, but when I said Power Query, I meant the interface that appears when you click 'transform data' and your tables appear listed to the left under the 'Queries' heading. The DAX created ones just don't seem to appear there and I can't find how to add them in / access them. If you know a way to access them in there for future reference, that would be greatly appreciated - or even if just closure confirming it's not possible, that would also be equally appreciated! I could have added a new column via DAX, with some long-winded formula to give them the right numbers, but to me it's such a bodge-job feeling workaround and not that flexible for any future changes.

[@MyOnlineTrainingHub]

Ah, DAX is the Power Pivot formula language. You would have created these tables in Power Pivot. Power Query is the tool available from the 'transform data' group of tools. I generally avoid using DAX to create tables and instead use Power Query to get the data or create the tables and then load to Power Pivot and stick to using DAX to write measures that don't result in tables.

[@ziedmasmoudi9846]

I am working with survey results with over 50 columns. Each column if for 1 question and includes answers like (very dissatisfied, dissatisfied, neutral, satisfied, very satisfied). Of course respondent's answers are not the same across all the questions. In order for this trick to work in my case, I will need to create a new sort order column for every single question on the survey, which is too much. Is there another approach that could be used in my case?

[@MyOnlineTrainingHub]

Sounds like your data is in the wrong layout. It should be in a tabular format so you only have one column for the answer and one column for the question number, among others. The correct layout is tabular, see this tutorial on tabular layouts: www.myonlinetraininghub.com/excel-tabular-data-format You can fix the layout by unpivoting it with Power Query: kzbin.info/www/bejne/Y3qwop6daJqUmqM

[@ziedmasmoudi9846]

@@MyOnlineTrainingHub Thank you for your prompt response! My data is already in tabular format. There is a way to create new tables with sums and do the analysis as intended but that would mean I cannot dig further into the data or use slicers and filters in Power BI to dig deeper.

[@MyOnlineTrainingHub]

Hi Zied, Please post your question and sample file on our forum where we can see your data layout and issue help you further: www.myonlinetraininghub.com/excel-forum

[@Kovac456]

Great video!! Got a question tho. What if i have multiple columns that need sorting, all have the same values just different headers(inside columns is Strongly Agree, Agree, Neutral, Disagree and Strongly Disagree). Do i need to make a sort column for each one or is there a way to apply one Sort column to all?

[@MyOnlineTrainingHub]

You'd only be able to sort by one of the columns at a time, so I'm not sure what you're wanting to do. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@bravucod]

Thanks, Mynda I love your videos, and it doesn't hurt that you're pretty too!

[@MyOnlineTrainingHub]

Oh thank you, Daniel!

[@moedadventurer1038]

Thanks Mynde

[@MyOnlineTrainingHub]

My pleasure :-)

[@myriamvanderzwalmen8730]

Thank you Mynda for this great tutorial. I sorted the order on my bar chart to what I wanted it to be. But I have a slicer on my page and when I use it, the order is only showing correctly for 1 of the three slicer options. For the other two options, the order gets changed again. Do you have any advice on how to keep the correct order for all slicer options? Any help would be greatly appreciated.

[@MyOnlineTrainingHub]

Sounds like your other Slicers are not using the same field that has the Sort by applied.

[@myriamvanderzwalmen8730]

@@MyOnlineTrainingHub Thank you for your help, I'll look into that as the root issue.

[@abdulkareemal-mahdi4288]

Thanks alot🌹

[@MyOnlineTrainingHub]

You're welcome 😊

[@s3d843]

Great video, but you didn't show how you created the month table. I'm new to Power BI and trying to use it for analysis at my job. We've got over 20M rows of data, smh.

[@MyOnlineTrainingHub]

The month table came from Excel. I just typed the month names and numbers, nothing special 😉

[@s3d843]

@@MyOnlineTrainingHub i will give it a shot

[@grahamparker7729]

Nice one Mynda

[@MyOnlineTrainingHub]

Cheers, Graham!

[@Kristianto007]

The result on my column not 1, 2, 3 but only null

[@MyOnlineTrainingHub]

Hard to say without seeing it. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@lck2978]

Wow, thank you for freely sharing such a wealth of information! Your videos are done so well! I'm still somewhat new to a lot of this. Would you be able to point me to an article/video, (or just tell me 😀) how to sort each cluster of columns by descending value? I can't paste a screenshot here, but I have a clustered column chart. Each cluster of columns is a year, and each column represents a work center. The columns sort alphabetically, but I want to keep them in their clusters (years) and sort them by value-descending (in this case, sort by which work center has the most scrap value in $$). I hope that makes sense. I posted on the Power BI community as well. Thank you, -Loren

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@markrustin7854]

Hello and thank you for your Video... love it. Could you please help with the following: 1st I have a matrix table with items that have been sold to my customers, this is sorted in the amount sold column to see what Items have the greatest sales value for my customers. However I have added a drilldown with month and year and require these months to be sorted not in sales value but now in month year order???? Is this possible ;-) Thanking you in advance Mark

[@MyOnlineTrainingHub]

Hi Mark, you'd have to reapply the sort based on the Month & Year fields. How about using a custom tooltip that shows the month-year view sorted as you wish: kzbin.info/www/bejne/mXjTc4iApauKqK8

[@markrustin7854]

@@MyOnlineTrainingHub Hello and thank you for your response. Currently I can only sort on my Sales figures... not sure how to reapply the sort base after I already have my figures in sales order and not in date order. If you have a video on this I will greatly appreciate the link. Thanking you once again. Mark

[@mathewrogers5430]

You guys are the BEST..! Thank you indeed because after I watched your video and applied it everyone looked at me like Superhero 🦸