Sort Legend by Chart Value

[amhiggins]

Hello,

I have a line chart showing the running count of sometime over time per category. There happen to be a lot of categories in my data set which is out of my control. Here is an example plot with the category names withheld (for security purposes). 

What I want is for the legend on the right to be sorted with the category with the highest value at the top - so the top line (as ordered by the current total) in the chart is also the top category listed in the legend, the second line is the second category listed, etc. 

I tried creating a summary table with the value counts (code below) and then using that to custom sort the category column but I get a circular dependency error because I'm using the category column to create the summary table. 

Category Sort Table = 
SUMMARIZE(table, table[Category], "count", CALCULATE(DISTINCTCOUNT(table[id])))

Is there a way to achieve this type of legend sorting? Am I missing something?

[Anonymous · Accepted solution]

Hi  @amhiggins ,

I created some data:

Here are the steps you can follow：

1. Enter the Power query, click Table, and click Copy to form Table2.

2. In Table 2, Home – Group By.

New column name – Max.

Operation – Max.

Column – amount.

3. Select the newly appeared [Max] and select Sort Descending.

4. Add Column – Index Column –From 1.

5. Place [Qtr] of Table in Axis, [amount] of Table in Values, and [group] of Table2 in Legend.

6. Click [group] of Table2, Column tools – Sort by column – Index.

7. Result.

Here it will be sorted with Index.

Best Regards,

Liu Yang

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

[amitchandak]

@amhiggins , as of now legend can sort ascending or on a sort column

[amhiggins]

And it is not possible to create a sort column in this situation?