Display legend order by Y-Axis value

Question

Hi PowerBI experts,I've a line chart as below:X-Axis are daysY-Axis are errors CountLegends are from Column "Errors" and the errors list might be different every day.&nbsp;I hope the chart order the legend by the count.&nbsp;For example,&nbsp;for data below, by default the line chart would order the legend by Errors'&nbsp;alphabetical sequence. What I hope is:1. If you select 12.06, the lengend order is [BError, AError]. If you select 12.07, the legend order is [CError,Error]. The legend is ordered by its count.&nbsp;&nbsp;Day &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Errors &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Count12.06 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;212.06 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1012.07 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2512.07 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp;&nbsp;&nbsp;I checked some discussions&nbsp;https://community.powerbi.com/t5/Desktop/Rearrange-Pies-in-Pie-Chart/m-p/25218#U25218 &nbsp;but this solution doesn't work for me as our legends would change every day and not fixed value.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

tringuyenminh92 · Answer

Hi&nbsp;huzhan,&nbsp;If count field is not a calculated measure field (could be calculated column or default value field), you could use Sort by column in Modeling tab -&gt; Sort by column -&gt; choose another order column to sort&nbsp;&nbsp;If this works for you please accept it as solution and also like to give KUDOS.Best regardsTri Nguyen

huzhan · Answer

Thanks , but this doesn't work for me:1. Count is the raw column from backend table, but the "Sort By column" in Modeliing tab is grey for me and not clickable&nbsp;2. If I click the&nbsp;ellipse at the chart and select sort by count, then the X-Axis would mess up. What I want is the X-Axis still sorted by Day

tringuyenminh92 · Answer

Hi huzhan,&nbsp;Please choose the field you want to sort firstly, after that, the sort by column button will be available.

huzhan · Answer

Hi&nbsp;tringuyenminh92, select the "Error"column and try to sort by column "Count" doesn't help. It throws exception below.&nbsp;I think it makes sense becaused for each Error, it would have different Counts on differnt dates.&nbsp;For example, if we select time window [2016-12-10, 2016-12-11] and the raw data as below:&nbsp;Date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Error &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Count2016-12-10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 102016-12-10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;192016-12-10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 82016-12-11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;252016-12-11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;152016-12-11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp;Then for 2016-12-10, the legend order should be [BError, AError,DError] (order by count desc)for 2016-12-11, the legend order should be [AError, BError, CError]. The Error-Count mapping is not m:1 mapping so it would throw error.&nbsp;Is there a way to create a new column/measure to sum[count] for selected time window for all errors appeared during that time then it would be a 1:1 mapping and we could sort by it? Still using examples above, It might look like below by adding a new "TotalCount"Timewindow &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Error &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TotalCount1210-1211 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (10+25)=351210-1211 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(19+15) = 341210-1211 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp;1210-1211 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp;

huzhan · Answer

Hi&nbsp;tringuyenminh92,Select the "Error" Fields and Select "Count" from Sort By Column doesn't work. It would throw excepations as below: &nbsp;I think this make sense as Same Errors might have different Count in different days, which might break the m:1 mapping required by the order. What's more, the errors type occurred in different days might vary as well.For example, if you select raw data from [2016-12-10, 2016-12-11]:Date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Error &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Count12.10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1012.10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1912.10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;512.11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2512.11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1012.11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp;&nbsp;Then order by count, for 12.10, the legend should be [BError, AError, CError]for 12.11, the legend should be [AError,BError, DError]This break the m:1 mapping and it makes sense for PowerBI to throw exception.&nbsp;My new question is this, is it possible to add a new column/measure or sth else to get a 1:1 mapping for Errors in selected time window? For example, if we could sum[count] per errors for selected time window, still use sample data above:TimeWindow &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Error &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Count1210-1211 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (10+25)=351210-1211 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (19+10)=291210-1211 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;51210-1211 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DError &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6Then the Error:Count is 1:1 mapping and the legend order is [AError, BError, DError, CError] based on it&nbsp;

Forum Discussion

Display legend order by Y-Axis value

6 Replies

Related content

Dynamic y axis legend

Line chart (y axis; seconday y axis and legend issues)

POWER BI LINE CHART LEGENDS WITH TWO MEASURES IN Y AXIS

Dynamic Y axis on DateTime X axis

Dynamic y-Axis calculation with Deneb