Display legend order by Y-Axis value

[huzhan]

Hi PowerBI experts,

I've a line chart as below:

X-Axis are days

Y-Axis are errors Count

Legends are from Column "Errors" and the errors list might be different every day. 

I hope the chart order the legend by the count. 

For example, for data below, by default the line chart would order the legend by Errors' 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. 

Day              Errors                      Count

12.06            AError                      2

12.06            BError                      10

12.07            CError                      25

12.07            AError                      5

I checked some discussions https://community.powerbi.com/t5/Desktop/Rearrange-Pies-in-Pie-Chart/m-p/25218#U25218  but this solution doesn't work for me as our legends would change every day and not fixed value. 

[tringuyenminh92]

Hi @huzhan,

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 -> Sort by column -> choose another order column to sort

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

[huzhan]

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 

2. If I click the 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]

Hi @huzhan,

Please choose the field you want to sort firstly, after that, the sort by column button will be available.

[huzhan]

Hi @tringuyenminh92,

Select the "Error" Fields and Select "Count" from Sort By Column doesn't work. It would throw excepations as below:

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                          Error                                  Count

12.10                         AError                               10

12.10                         BError                                19

12.10                         CError                                5

12.11                         AError                               25

12.11                         BError                                10

12.11                         DError                                6

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.

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                        Error                         Count

1210-1211                            AError                       (10+25)=35

1210-1211                            BError                       (19+10)=29

1210-1211                            CError                        5

1210-1211                            DError                        6

Then the Error:Count is 1:1 mapping and the legend order is [AError, BError, DError, CError] based on it

[tringuyenminh92]

Hi @huzhan,

I just think alike you, but as you said that we need to make the order column(count) unique or 1-1 with field of legend. I'm still thinking.

[huzhan]

Hi @tringuyenminh92, select the "Error"column and try to sort by column "Count" doesn't help. It throws exception below.

I think it makes sense becaused for each Error, it would have different Counts on differnt dates. 

For example, if we select time window [2016-12-10, 2016-12-11] and the raw data as below:

Date                     Error                       Count

2016-12-10          AError                     10

2016-12-10          BError                      19

2016-12-10          DError                     8

2016-12-11          AError                      25

2016-12-11          BError                      15

2016-12-11          CError                       5

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.

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                   Error                       TotalCount

1210-1211                    AError                     (10+25)=35

1210-1211                    BError                      (19+15) = 34

1210-1211                    DError                     8

1210-1211                    CError                       5