EVERYTHING YOU need to know about SORTING in POWER BI

Рет қаралды 56,770

Күн бұрын

Sorting is not always as straightforward as you might think. This video covers everything you need to know about sorting in Power BI with practical examples on:
- sorting on multiple columns
- sort by
- sorting columns headers on matrix visuals
- using fields that are not on a visual for sorting
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZfaq: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX

Пікірлер: 94

@paulstahl3162 3 жыл бұрын

Every time I watch one of your videos I learn something new. Your content is up there with the best quality and information available. Thank you for your hard work and I look forward to seeing your channel blow up to the subscribers and views it deserves!

@HowtoPowerBI 3 жыл бұрын

that's so nice to hear, really appreciate the nice words! :D

@SalimAwadh-2906 Жыл бұрын

2023 and still relevant, learning new tips with every video. Keep up the good work.

@kkolady 10 ай бұрын

Loved the detailed walk through on all the scenarios. Great job!

@mandlamasilela3743 5 ай бұрын

Thank you so much just solve a 2 day problem. Your work and knowledge is wealth

@veronicazapotoczny7 2 жыл бұрын

Thanks for your videos! Are you able to show a video on how to sort legends dynamically based on value, for a bar chart? Considering we have: the axis as years, the values as budget, and the legend as 40+ categories, I am wondering if there is a simple(r) way to achieve this. Thank you, love your videos!!!

@CJ-jc8tn 3 жыл бұрын

Thank you Bas! Another brilliant video. I initially wondered do we really need a 16 minute video on sorting in Power BI? Boy, was I wrong! I learned so much in all 16 minutes.

@HowtoPowerBI 3 жыл бұрын

haha fieewww, good to hear that I didnt waste 16 min of your time :D

@flyeagle320 Жыл бұрын

I cant say enough thank you helping me on this Bas . Thanks a ton

@bhargavreddy6423 3 жыл бұрын

I like the way how you teach. you are a great teacher. Thanks!

@mfella8065 Жыл бұрын

Exactly what I needed to resolve an issue I had. Your amazing and psychic 😂👍

@sineadkay978 Жыл бұрын

This is super helpful, thank you!

@atiry839 3 жыл бұрын

This is great video. thank you.

@asam Жыл бұрын

Awesome - So easily you can Sort what you want just by adding the - (minus) Thank you!

@aravindkumarkalicheti3014 2 жыл бұрын

this was so help full.. please make a video on total issues matrix and table no one is done on those issues perfectly..

@DHARMYOGCOM Жыл бұрын

nice video. is there a way to turn off sorting?

@Bala2822 Жыл бұрын

Love your work Bas, I have learnt so much by watching and following your content. I have a question! I work in the research and insights industry, and we tend to use a lot of Likert scales, if a survey has 5/6 5-point scales, it would be handy to sort them by adding just one new column and referencing all of them to the one column as the data structure is mostly the same and it will save a lot of time. Is there any way to do that? or would you recommend another way to approach it?

@VenkyChalla123 3 жыл бұрын

No words to say. I was wondering on web for this type of sorting techniques Thank you very much

@HowtoPowerBI 3 жыл бұрын

Thx for watching Venky!!! 😀

@user-xi7cj3so8f 2 жыл бұрын

thanks for your videos, they are very helpful. Can you please tell me if there is a way to sort columns within a matrix? You can sort the totals, but what about the intermediate data? In your example on a matrix, is it possible to sort the columns 2020/2018/2019 in ascending order somehow?

@robrayborn1349 3 жыл бұрын

Very helpful. I had needed to reverse my Yearly column order. You gave me the answer. I would like to be able to add Quarters to this as well, but only show the quarters for the current month, the previous 3 years would only be the yearly quantity. How would i do this?

@MatthewJQuintero 2 жыл бұрын

Hello, Thank you so much for all of your wisdom. I have a quick question though, on all of my reports now I can't sort unless I make the column widths crazy wide. When I hover over the column, it gives me only the option to adjust the width, not sort. Any ideas on what I did? Thank you so much.

@TheBebwa 8 ай бұрын

Hey Bas, Thank you for your videos. I've been struggling with figuring out a way to sort my column headers (in a matrix table) dynamically based on a slicer selection. Let's say I have Product Names as column headers and product A has been selected in the slicer. Rather than have my matrix show only Product A values, I would want the Product A Column appear first in the matrix table and the other products come after

@frederickzoreta6617 2 жыл бұрын

Thanks a LOT for your tip on sorting with the column chart! You definitely saved 2 of my reports! Now, my bosses are NOT going to shout at me coz of you! haha. SERIOUSLY, thank you.

@seamushand8439 Жыл бұрын

Thank you Bas - im struggling with how to sort a stacked bar chart largest to smallest

@VikasTheAnalyst Ай бұрын

Thanks A lot Bass.

@zabawna100 5 ай бұрын

Great! Thanks!!!!

@F_-vm3ns Жыл бұрын

Awesome Video, i have a question is it possible to do the sort by function as a slicer, so its directly visible for the user?

@razainthetube 2 жыл бұрын

Thanks for such a wonderful video. I'm stuck at a sorting problem. I have multiple columns say cities with numbers in the matrix, the data changes everyday and so should the order of columns header based on the matrix values in descending order (left to right). Any idea what's the easiest way to workaround. I created a table in my excel using the pivot and sorted it there, then added an index column in power BI. It is working fine, but everytime the new data is updated, I need to manual change the pivot filters, refresh them to the new sort order. Would be glad to hear back.

@andrewcoventry7246 Жыл бұрын

One method that I have found useful for the sort order is rather than adding another column to my fact table, I like to create the relationship from the fact table to the order table then use the categories in the order table rather than use the fact table at all. But as always just depends on the your data model and size of data.

@JRZh Жыл бұрын

thanks a lot, it's so helpful

@HowtoPowerBI Жыл бұрын

:) glad you think so!

@sivamani9752 3 жыл бұрын

Super, it would be helpful if you show how to sort the columns based on the grand total in the bottom. Eg: left coulum with customer name, top columns are with few sales rep names and in the table it will have each sales reps sales amount for each customer, now i want to sort the sale rep based on the total sales done by each rep( total in the bottom)

@HowtoPowerBI 3 жыл бұрын

The sorting of the column headers in a matrix visual can be set by the Sort By Column. So you would need to add a column with a SortID based on sales and use that.

@barttitulaerexcelbart9400 3 жыл бұрын

Thank you Bas, these are typically examples you lose a lot of time to fix. One remark: Maybe make the sort column dynamic, so the order is dependent on the revenue for example. Maybe use someting like RANKX ? Another pitfall might be that for example in 2019 the Cellphones is the largest group, then the ideas of the report maker are not that obvious. But bottom line: nice video!!

@HowtoPowerBI 3 жыл бұрын

oh I like that idea :) worth a video on its own. Thanks!

@Bharath_PBI 3 жыл бұрын

That's great. Covered almost all the sort options. How do we sort (sort by column) for the scenario below? Users need dynamic switching of axis in column charts. So to accomplish this, we can set up the table by pivoting the required columns (which originally had the sort order defined) to a new table. This new table (connected or disconnected table) pivoted column needs to be having the same sort order as it's in the base table. Ex: category and subcategory are individually having sort order columns, but after pivoting their data values reside in a single column. So category="Audio" has sort by value as 1 along with subcategory="Audio Book" also has sort by value as 1. Any thoughts?

@TheBebwa 8 ай бұрын

Did you figure this out?

@totvabe1 3 жыл бұрын

Brilliant as usual!

@HowtoPowerBI 3 жыл бұрын

Thank you so much :)

@anaballesteros-sosa4007 3 жыл бұрын

Thanks. Very clear

@HowtoPowerBI 3 жыл бұрын

thx Ana for watching 🙂

@mathewinmuscat 3 жыл бұрын

Brilliant, like all other videos....Thanks

@HowtoPowerBI 3 жыл бұрын

thank you!!! :D

@fauzanmauludy7973 3 ай бұрын

Hey what if we want to sort Continous Time (Hourly) by another columns? Thankyou

@rogerloydmccoy 11 ай бұрын

i would give this video 1000 likes if it were possible. Thanks very much!!!!

@SaraWilde100 27 күн бұрын

Hi Bas, how do i sort the rows in a matrix based on the values in one column. For example I have a matrix of customers in the rows, 3 years in the columns and Invoiced sales in the values. I need to give the users the ability to choose one of the 3 columns to sort the rows in descending value. If they use the Sort By "invoiced Sales" it sorts it based on the total sales value across all three years, they cannot select just one year

@gondebas Жыл бұрын

Hi, what if you have multiple columns in Power BI matrix rows and you want to sort some of them ascending and some of them descending? Say you got Brands and Products in the matrix row and you want to sort Brand asc and Products desc. My question is about the expanded matrix view.

@vinothparker7425 2 ай бұрын

In case if we have used multiple fields lets say three in columns of a matrix visual. How do we sort the three layer of headers?

@markociric5682 2 жыл бұрын

How to sort formatted values? Thanks in advance.

@venkatareddykummitha5863 2 жыл бұрын

I have 20 unique kpi's that I need to sort by creating calculated column in same table. HOW is that & is it good than in query editor. How to sort by using calculated column. Pls explains me

@annkrause9325 3 жыл бұрын

Great information! I have a report where I need to sort by two values: sort desc by one of them and sort asc by the other at the same time. Do you know of a way to do that?

@HowtoPowerBI 3 жыл бұрын

thx Ann, if it is in a table you can hold the shift key and then click on the headers of the columns you want to sort by

@vidya2218 7 ай бұрын

Hey , How to sort the sections of stack column charts based on values.I need to make the largest on the bottom and smallest on the top. Please note i am not talking about the legend.I dont want to sort legend .I want to sort values.Usual sorting isnt working. Could tou please help me out!!!!

@Simon-vc1wk Жыл бұрын

What if you had a matrix populated by many measures. You could use rank x measure in dax by several categories but seems complex to achieve something that should be easy

@markrustin7854 Жыл бұрын

Hello and thank you for your Video... I love them. 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... down to the least for all 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 ;-) So just to explain further I can see my top customers based on sales value (sorted in descending order), then I can drill down and see their products bought... this is also sorted in sales value order (perfect). Then I can drill down further to month year but this is also sorted in sales value order and not month year like I need it ;-) Thanking you in advance Mark

@yvsrinivasan23 Жыл бұрын

How to sort matrix visual with row header s

@MsLOLLAWL 3 жыл бұрын

Hello! Is there a way to custom sort column headers in a matrix? For example if I have columns A, B, and C it is automatically sorting that way on the headers. However, I want the columns to display as B, A, C. Can you help?

@HowtoPowerBI 3 жыл бұрын

Hi Andrea, yes you use a sort by column to set the order to B, A, C , see video for details.

@fiorella8581 3 жыл бұрын

And what is the workaround if I need custom order for 3 column? I mean, I have a matrix with 3 rows (example: continent, country, city), some columns and values. I would like to have custom orders for the 3 columns, first of all Continent, then country and finally city. I've tried using two index order one for continent and one for city. It works for continent but not for city. thanks a lot

@HowtoPowerBI 3 жыл бұрын

I have just tried it on some sample data and it works. Could you send me an example on which it doesn't then I can have closer look (bas.dohmen@datatraining.io)

@fiorella8581 3 жыл бұрын

@@HowtoPowerBI thanks for your answer. At the end, I solved my issue, i used 2 order columns rather than 3. Thanks a lot

@daxteoh27 Жыл бұрын

Is there anyway to sort matrix individual column? say using table 10:58 - to sort 2020 in either ascending or descending order.

@SaraWilde100 27 күн бұрын

Did you ever find a way to do this? I am trying to do the same and can't find a solution

@bhabaniprasadpanda102 3 жыл бұрын

I have a column having text data type and contains text and numbers .sorting was not working on that column ,anything I am missing ?

@HowtoPowerBI 3 жыл бұрын

hm 🤔should work. Could you send me a sample so that I can c what the problem is - bas.dohmen@datatraining.io

@EL-tirol Жыл бұрын

The solution is nice but too static for tables where proportions of categories vary each month. I think in this case it would be better to create another table, populate it with category name and the desired measure, then group it and order it. After that we can merge this grouped table to the initial one and sort the visual by the measures that are used.

@bhaskardwivedi8484 3 жыл бұрын

Hi, have been following your method of sorting but not able to get the desired output. I need to have the first month as April and then onwards. I inputted the sort order as well and then I am choosing to sort my month by that sort order also. Still I am getting january as the first month. Can you please help?

@HowtoPowerBI 3 жыл бұрын

not sure where you went wrong, sounds correct. You can send the example to bas.dohmen@datatraining.io and I'll have a look

@VenkyChalla123 3 жыл бұрын

I have a question, what happens if the new category is added into the column. It will automatically sort based on conditional column that we derived? or we need to modify the conditional column?.

@HowtoPowerBI 3 жыл бұрын

If a new category is added then you want to make sure that its sort column is also taking into account that new item. The sort will happen automatically also when there are new items.

@VenkyChalla123 3 жыл бұрын

Can u short the category column by creating a new column with the help of DAX If yes please make a video

@VenkyChalla123 3 жыл бұрын

Thank you

@mohanit633 2 ай бұрын

May I know what to be done inorder to Not to show the hidden column which is used for sorting while exporting in table viz .

@HowtoPowerBI 2 ай бұрын

will show in the export unfortunately

@mohanit633 2 ай бұрын

@@HowtoPowerBI thx for the prompt response However my customer don't want to see it ...will try to convince 😁

@divyangchoudhary306 2 жыл бұрын

For a matrix, is it possible to sort by the Row totals?

@HowtoPowerBI 2 жыл бұрын

add a calculated column with the sort /rank you need. Add it to the matrix and expand down.

@kamalgopalsingh244 3 ай бұрын

Hi I have one situation I created Customer wise Sales Table Visualition but I have to sort that By profit % but dont want to show Profit % in Table Viz Is this achievable ?? if yes then How ??

@HowtoPowerBI 3 ай бұрын

add it to the table, turn auto size columns off from values and column header then you could make the column 0px wide so that it is not visible... now you can apply the sort

@TheCruelkindness 3 жыл бұрын

How to sort stacked area chart with latest values?

@HowtoPowerBI 3 жыл бұрын

you can just sort by date

@NinaHulmanova 2 ай бұрын

I keep getting error " there can't be more than one value in Sorting column for the same value in XXX column" when I try to sort it. I tried many different ways of creating the sorting column. Any idea what might be the problem?

@HowtoPowerBI 2 ай бұрын

The error message gives it away. There cannot multiple values in the sorting column for the same item in the other column.. if you already checked that and still get an error then it is most likely cause of blanks