Keep legend order in stacked column chart with slicer selections

[vrajkumar1]

How do I maintain the legend order within a stacked column chart in power bi. The column is sorted using a specific column sort in ascending order. This page also has slicers at the top. So when I select specific values in any slicers this sort order changes. How can I avoid this.

I did not try anything as I did not know what to do when I change my slicer selections.

[v-jianpeng-msft]

Hi, @vrajkumar1 

Based on your description, I created the following sample data:

In a stacked column chart, the legend is sorted as shown below:

I perform fixed legend sorting through the following method:

1.First, I used the switch function to define an order for the fields in Type

sort = SWITCH('Sheet5'[Type],
    "Sent",1,
    "Over",2,
    "Missing",3,
    "Receive",4
)

2. I used the following DAX to copy the Type column for easy comparison with the original Type:

Type copy = 'Sheet5'[Type]

3.I sort the Type copy column using the sort column, as shown in the picture below:

4.Put the Type copy column into the chart to fix the sorting of legends:

Original sorting VS fixed sorting:

Now the legends are sorted in the order I predetermined, rather than randomly. I've provided the PBIX file I used below, it would be great if it helps you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[vrajkumar1]

Thank you for your response.

I did exactly how you have stated.  It worked fine with no slicers.  When I added slicers on that page and made slicer selections the original sort order was not maintained. 
Please see the 2 images - Original Sort - which is how I want.  Second image - after I made a slicer selection the order changed

Original Sort - Works correctly.

Sort with Slice Selections the sort order is different from above.  The percents are not in the order set. Light blue 28.3 should be the bottom most legend not 24.8 for Jan.

[marcelsmaglhaes]

Hey @vrajkumar1 

To maintain the legend order within a stacked column chart in Power BI, you can use the "Sort by Column" option in the field settings. This will allow you to specify a specific column for sorting the legend items. However, when slicers are applied, the sort order may change if the slicer selections affect the underlying data.

To avoid the sort order changing when slicer selections are made, you can try the following approaches:

1. Disable Slicer Interaction: You can disable slicer interaction for the visual by selecting the visual, going to the "Format" pane, and under "Edit interactions," selecting "None" for the slicer visual. This will prevent slicer selections from affecting the sort order of the legend items.
   
   

2. Sort by a Calculated Column: Create a calculated column in your dataset that assigns a numerical value to each category based on the desired sort order. Then, use this calculated column to sort the legend items. This way, the sort order will remain consistent regardless of slicer selections.
   
   

3. Use a Static Sort Order: If the number of legend items is limited and doesn't change frequently, you can manually specify the sort order by creating a static sort order column in your dataset and using it to sort the legend items. This approach ensures that the sort order remains consistent regardless of slicer selections.
   
   

4. Bookmark and Selection Pane: If your report layout allows, you can use bookmarks to save specific slicer selections along with the desired legend sort order. You can then use the Selection Pane to show/hide visuals based on the bookmarked state, ensuring that the legend order remains consistent for each bookmarked view.
   
   

5. Use Bookmarks with Sorting: Create bookmarks for different slicer selections along with the corresponding legend sort order. This way, users can easily switch between different views while maintaining the desired legend order.

By implementing one or a combination of these approaches, you can maintain the legend order within a stacked column chart in Power BI, even when slicer selections are made. Choose the approach that best fits your specific reporting requirements.

[vrajkumar1]

Thank you for your response.

I opted for Option 2.  But it worked fine on its own.  When I had slicers on that page and made slicer selections the original sort order was not maintained.