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
rintoalappat
Frequent Visitor

Help Needed in Nested Sorting for Hierarchical Bar Chart

Hi Everyone, 

I'm a begginer in Power BI, We are working on a Tableau to Power BI Migration project where I'm facing some difficulties in converting one bar chart to power bi. 

 

Scenario : I have a category and subcategory and sales value plotted in a bar chart, I have turned of Concatenation, I want to show it by Category group (Highest Category First) then inside each group show sucategory bars (Highest Subcategory First).

 

I was able to achieve the first sort by creating a Total colum using ALLEXCEPT(). but I'm not able to do the second layer sorting. 

Any help is appreciated. 

 

Screenshot below:

 

Screenshot 2023-11-20 221413.png

 

Expected :  I want to show highest subcategory in DESC in each category. 

 

Sample File Download Link : Sample PBIX File 

 

Regards,

Rinto

1 ACCEPTED SOLUTION

15 REPLIES 15
Ahmedx
Super User
Super User

is this whate you looking for

Screenshot_1.png

yes, This is what I'm looking for. Could you please share the steps which you followed. Thanks in Advance. 

1.png2.png3.png4.png

Hi, I have the same Issue. I can open the bar graph dashboard. Could you send in another version? Thanks!

 

Hi, I am following the steps and i got an error:

 

Failed to resolve name 'ROWNUMBER'. It is not a valid table, variable, or function name.

Thank you. Its working for me. I do have one more query in the same DAX, on the same bar chart, I have visual level measure filter which restricts the data for sales >=1.  

 

The abpve dax is working without the filter, but if I add that visual level filters, the sort s not working properly. I know we have to add that filter somewhere inside the m2 calculation summarization part. 

 

Could you please help me with that as well ? Appreciate your help and time.

if you find that you have a lot of subcategories and because of this the measure is violated, then try this measure
You can replace 100 with 1000 if you have a lot of subcategories

Screenshot_6.png

I don’t understand, please share the file with the problem

Scenario : Need to show subcategories which having Sales more than 50. 

Problem : When I apply visual level filter, its resetting the sort.

PBI file : Sample PBI File - Filter Interfere with Sort  

Error.png

parry2k
Super User
Super User

@rintoalappat great minds are already here and provided the appropriate solutions, just curious, is this what you are looking for:

 

parry2k_0-1700541334960.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

yes, subcategory is showing properly. but office supplies category should come at the beginning as per value desc.

Ritaf1983
Super User
Super User

Hi @rintoalappat 

Unfortunately, this functionality is unavailable.

Please refer to the linked discussion:

https://community.fabric.microsoft.com/t5/Desktop/Sorting-by-multiple-columns-in-clustered-column-ch...

And vote for the linked idea:

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=facafdf4-1c63-4db8-a385-c120e4607c45

To rank the values hierarchically you can use a matrix with conditional formatting.

It is also a much more efficient graph for ranking scenarios than  a clustered column:

Ritaf1983_0-1700538942576.png

 

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Thank you for the alternate solution. I do have some stacked legeds as well on the bar, I believe its not possible with Matrix bar visual

Hi @rintoalappat 
No, and don't recommend using stacked bars.
Please refer to my linked blog post which explains why.

"Home experiments" and stacked bar graphs (madeiradata.com)

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
amitchandak
Super User
Super User

@rintoalappat , When you sort ox Axis Category / subCategory, As of now you do not have option to sort subCategory based on value

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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.