cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Sorting Legend in a Line Chart

Problem: I cannot sort the legends based on the chosen category's sales proportion, PBI does it based on alphabetical order. Strange given you would have expected this to occur by default like excel. The idea is that whatever category/legend is selected (segment, brand or nameplate etc.), it is able to sort itself in descending order of % sales.

So Far: 
a) If I try to sort by clicking on the menu on the top right hand corner, and selecting 'Sort by % Sales', then it sorts the months on the X-Axis instead.
b) If I use Visual Level Filters, go to Sales, select 'Top N' and select Top 5, while it does hide the remaining Segments, it also recalculates the '% Sales' which gives the incorrect proportions.

How do I sort the legend by value AND show only a select set of things (e.g. maybe Top 5 Segments, but then it could be a chart that has select set of key brands etc.)?

Measure: % Sales = [Monthly Total]/CALCULATE([Monthly Total],ALLSELECTED())

 

 Sales Table.PNGCapture.PNGCapture.PNG

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Sorting Legend in a Line Chart

So, you need to create a numeric column that has them in the desired numeric order and then change the Sort By column of your alphanumeric column to that new numeric column.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
New Member

Re: Sorting Legend in a Line Chart

@Greg_Deckler - tks a lot for the quick reply. Actually I've been tracking this for many days and have seen replies on this, some of them from you too 🙂

 

The problem I face is, I want to do this dynamically. The sales data gets updated monthly. So the next month if any of this ordering was to change, how do I build that column to account for that? Lastly, in this chart it's car segments (e.g. Compact Car, Compact SUV etc.). If I am making the same chart for say brands (Chevy, Ford, Honda etc.) is there a way that the 'numeric column' you mentioned, can account for it?

 

Or what would you suggest as a turnaround?

 

Tks again for your time!

Highlighted
Resolver II
Resolver II

Re: Sorting Legend in a Line Chart

If you are looking for the last reported month ranking, you could add calculated column which would use data using filter of last month from today's month. 
It should work for your categorical filters as well. It will not work for date filters if you need them.

Highlighted
New Member

Re: Sorting Legend in a Line Chart

@alena2k - Tks! As you can guess I am still a newbie so would appreciate if you could post a sample formula of sorts of how I would define that new column?

 

Appreciate your time!

Resolver II
Resolver II

Re: Sorting Legend in a Line Chart

I hope that something like this will help you:

 

Last Reported Rank =

          RANKX(Categories,
                          CALCULATE(SUM('Sales'[Sales]), 'Sales'[Date] > EOMONTH(NOW(), -1),'Sales'[Date]<= EOMONTH(NOW(), 0))
         ,,DESC)

I assume that yiy have Caregories table where you can setup sorting by this column.

 

Highlighted
New Member

Re: Sorting Legend in a Line Chart

Thanks @alena2k...sorry I just saw this so will try this now. Will update soon.

Highlighted
Frequent Visitor

Re: Sorting Legend in a Line Chart

Any luck with the solution provided? I have been trying to solve (almost ) the exact same problem for last couple of weeks and can't seem to hit it right. I try sorting by column but it gaveme an error

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors