Forum Discussion

darko861's avatar
darko861
Resolver II
5 years ago

Line chart legend based on highest values

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!

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

     

    • darko861's avatar
      darko861
      Resolver II
      5 years ago

      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?

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    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's avatar
      darko861
      Resolver II
      5 years ago

      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.