Maintain Specific Order of Measures in a Stacked Column Chart

[LKBoldrino]

I have 4 measures in my dashboard, and I want to display them in a stacked column chart in a specific order.

How can I achieve this?

Even though my legend has the correct order, the chart displays them randomly.

I want the same order as in my legend.

I want "ZKB netto" to be the first in the stack at the top, followed by "LSB", then "ZSB", and "TGZ" at the bottom.

[LKBoldrino]

I tried this with fieldparameter but then i cant use the stacked column chart :

Measure Order Parameter = {

    ("ZKB Netto", NAMEOF('_MS-Tabelle'[MS_ZKB_Netto_Gesamtjahr]), 0),

    ("LSB", NAMEOF('_MS-Tabelle'[MS_LSB_Gesamtjahr]), 1),

    ("ZSB", NAMEOF('_MS-Tabelle'[MS_ZSB_Gesamtjahr]), 2),

    ("TGZ", NAMEOF('_MS-Tabelle'[MS_TGZ_Gesamtjahr]), 3)

}

Measure order Selection =

DATATABLE (

    "OrdersParameter", STRING,

    "Category", STRING,

    "Index",INTEGER,

    {

        { "ZKB Netto", "A",1},  

        { "LSB", "A",2},

        { "ZSB", "A",3},

        { "TGZ", "A",4}

    }

)

[grazitti_sapna]

Hi @LKBoldrino ,
In Power BI, the order in which measures appear in a stacked column chart is determined by the order of their values in the data, not the legend. To ensure a specific order for the stack, such as "TGZ" at the bottom and "ZKB netto" at the top, follow these Steps to Implement the Custom Table and Join:

1. Create a Custom Table for Measure Order: In Power BI, create a new table with your desired measure order. Use the following DAX expression to create it:

   • 
     MeasureOrder = 
     
     DATATABLE(
     
     "Measure", STRING,
     
     "Order", INTEGER,
     
     {
     
     {"TGZ", 1},
     
     {"ZSB", 2},
     
     {"LSB", 3},
     
     {"ZKB netto", 4}
     
     }
     
     )

2. Unpivot Measures (If Necessary): If your measures (e.g., TGZ, ZSB, LSB, ZKB netto) are in separate columns:
   • Go to Power Query Editor.
   • Select the measure columns.
   • Right-click and choose Unpivot Columns.
   • This will convert your measures into two columns: Attribute (measure name) and Value.

3. Join the MeasureOrder Table:

   • Go to the Model view.

   • Create a relationship between the Attribute column (or equivalent) in your unpivoted table and the Measure column in the MeasureOrder table.

4. Sort by the Custom Order:

   • In the MeasureOrder table, select the Measure column.

   • Use the Sort by Column option and choose the Order column.

5. Update the Stacked Column Chart:

   • In your visual, add the Value column to the Values field.

   • Add the Measure column (from the MeasureOrder table) to the Legend field.

   • The stacking order in the chart will now respect the custom sort order defined in the MeasureOrder table.

6. Optional: Legend Sorting:

   • To align the legend order with the stacking order:

     • Select the chart.

     • Click the More options (three dots) on the legend.

     • Sort the legend by the Order column from the MeasureOrder table.

This can work because by joining the MeasureOrder table with your unpivoted data, Power BI can enforce the custom sorting logic using the Order column. The stacking order in the chart will follow the order defined in the MeasureOrder table, ensuring that "TGZ" is at the bottom and "ZKB netto" is at the top.

This approach is clean and scalable, especially if you need to update the order or add new measures later. You can Also Refer to this Youtube Video : Link
If I have resolved your question, please consider marking my post as a solution. Thank you!

[LKBoldrino]

My measures are in my measure table and not in columns. How can I unpivot them?

[Kishore_KVN]

Hello @LKBoldrino ,

It is because LSB value is negative and sorting will be either ascending or descending. In this case use can use modulus of the value and mention using conditional formatting for the values which are below 0.

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