Line chart legend based on highest values

[darko861]

Hi,

I have a line chart that shows the performance of the top 10 members based on the highest number of hours they have consumed over 8 months. The Legend is sorted in alphabetical order but I want it sorted by rank instead (the member with the highest value at the top, in descending order) and keeping the dates in chronological order.

What is the easiest way to do this? The values in the chart are based on a measure 

Total Hours = SUM(v_rpt_Time[Billable_Hrs]) + SUM(v_rpt_Time[NonBillable_Hrs])

from the V_rpt_time, which  is the main data source

The members are from the table v_rpt_Members and the dates are from the calendar table.

All of the tables are related with 1 to many relationships.

If I need to insert a calculated column with a formula, then please advise how and where to insert it? I guess then I would be able to sort by column? If there is a better and more efficient way to do this, then please advise how I should write e.g a new measure or something else in order to get the expected outcome?

Thanks in advance!

[jaryszek]

Up, 

anybody solve the case? 
@darko861 did you replace your visual with different one? 


Best,
Jacek

[v-yalanwu-msft]

Hi, @darko861 

According to your description，I think you can add a rank column, then merge the rank and member column and name it rank_member,and finally change the legend of the line chart to rank_member. The steps are as follows:
step 1: Add column(rank)

rank =
RANKX (
    'Table',
    MAXX (
        FILTER ( 'Table', [member] = EARLIER ( 'Table'[member] ) ),
        [v_rpt_Time[Billable_Hrs]]] + [v_rpt_Time[NonBillable_Hrs]]]
    ),
    ,
    DESC,
    DENSE
)

step 2: Add column (rank_member)

rank_member =
'Table'[rank] & "-" & 'Table'[member]

step 3:Change legend

Best regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[darko861]

Hi @v-yalanwu-msft , this does not seem to work. I have followed your steps, but you have to remember that your ranking formula is applied to the whole dataset in v_rpt_time data, which means all of the dates from 2019 to 2021. So if I want to showcase a specific date range it does give me the top 10 but it's still in the wrong order since the ranking is not dynamic or applied to that specific date range.

[amitchandak]

@darko861 , I doubt you can sort legend based on Rank. you can only sort by alphabetical order or Sort column.

[darko861]

Hi @amitchandak, so do you think I should leave it as it is, or is there any other method I could just try, where the members with the highest values are shown first?