EVERYTHING YOU need to know about SORTING in POWER BI
Vložit
- čas přidán 5. 07. 2024
- 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 CZcams: 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 - Věda a technologie
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!
that's so nice to hear, really appreciate the nice words! :D
Loved the detailed walk through on all the scenarios. Great job!
2023 and still relevant, learning new tips with every video. Keep up the good work.
Thank you so much just solve a 2 day problem. Your work and knowledge is wealth
I like the way how you teach. you are a great teacher. Thanks!
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.
haha fieewww, good to hear that I didnt waste 16 min of your time :D
I cant say enough thank you helping me on this Bas . Thanks a ton
Exactly what I needed to resolve an issue I had. Your amazing and psychic 😂👍
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!!!
This is super helpful, thank you!
Awesome - So easily you can Sort what you want just by adding the - (minus) Thank you!
This is great video. thank you.
this was so help full.. please make a video on total issues matrix and table no one is done on those issues perfectly..
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?
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?
No words to say. I was wondering on web for this type of sorting techniques
Thank you very much
Thx for watching Venky!!! 😀
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?
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.
Thanks A lot Bass.
Brilliant as usual!
Thank you so much :)
thanks a lot, it's so helpful
:) glad you think so!
Thanks. Very clear
thx Ana for watching 🙂
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.
nice video. is there a way to turn off sorting?
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
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?
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.
Thank you Bas - im struggling with how to sort a stacked bar chart largest to smallest
Great! Thanks!!!!
Brilliant, like all other videos....Thanks
thank you!!! :D
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?
Did you figure this out?
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)
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.
Hey what if we want to sort Continous Time (Hourly) by another columns? Thankyou
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
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.
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?
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
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.
How to sort formatted values?
Thanks in advance.
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?
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!!
oh I like that idea :) worth a video on its own. Thanks!
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!!!!
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
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
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
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?
Hi Andrea, yes you use a sort by column to set the order to B, A, C , see video for details.
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
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)
@@HowtoPowerBI thanks for your answer. At the end, I solved my issue, i used 2 order columns rather than 3. Thanks a lot
i would give this video 1000 likes if it were possible. Thanks very much!!!!
Is there anyway to sort matrix individual column? say using table 10:58 - to sort 2020 in either ascending or descending order.
Did you ever find a way to do this? I am trying to do the same and can't find a solution
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.
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 .
will show in the export unfortunately
@@HowtoPowerBI thx for the prompt response However my customer don't want to see it ...will try to convince 😁
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?
not sure where you went wrong, sounds correct. You can send the example to bas.dohmen@datatraining.io and I'll have a look
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?.
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.
Can u short the category column by creating a new column with the help of DAX
If yes please make a video
Thank you
I have a column having text data type and contains text and numbers .sorting was not working on that column ,anything I am missing ?
hm 🤔should work. Could you send me a sample so that I can c what the problem is - bas.dohmen@datatraining.io
How to sort matrix visual with row header s
For a matrix, is it possible to sort by the Row totals?
add a calculated column with the sort /rank you need. Add it to the matrix and expand down.
How to sort stacked area chart with latest values?
you can just sort by date
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 ??
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
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?
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
I want to know how you will sort the Data in a column as
Audio
Computer
Cellphones
Audio
Computer
Cellphones
Please help me out
you have to create a column with the sort order and then sort the product category field by that column .. see video for more details
I sort, and after sorting, it switches
too much time just to sort
thank you for making this video. can't see anything on the powerBi because your face is taking most of the screen. sorry
Thanks for watching and your feedback!
Why are you on the screen so much. We don't need to see you, we need to see what you're doing, not just hear you talk about it.
That’s the editing style I chose. There are many other channels that show only screen, maybe this would be a better fit for you.
Do you think this will ever end? ( I was referring to your hacks Mr Bas )