Stacked Column chart - Custom sort X axis based on Count of legend

Hi,

As per our understanding you're trying to sort the X-axis in a stacked column chart based on the count of a specific category (e.g., "Critical"), then break ties using "Medium", and finally "Low". Since Power BI does not allow direct sorting based on legend values, here’s a complete workaround using calculated measures and a helper table.

Steps to Follow

1. Create Measures

Go to Modeling → New Measure, and add:

CriticalCount = CALCULATE(COUNT('InstanceData'[Instance]), 'InstanceData'[Category] = "Critical")

MediumCount = CALCULATE(COUNT('InstanceData'[Instance]), 'InstanceData'[Category] = "Medium")

LowCount = CALCULATE(COUNT('InstanceData'[Instance]), 'InstanceData'[Category] = "Low")

SortOrder = [CriticalCount] * 10000 + [MediumCount] * 100 + [LowCount]

2. Create a Sorting Table

Go to Modeling → New Table:

InstanceSortTable =

SUMMARIZE(

    'InstanceData',

   'InstanceData'[Instance],

    "SortOrder",

        CALCULATE(COUNTROWS('InstanceData'), 'InstanceData'[Category] = "Critical") * 10000 +

        CALCULATE(COUNTROWS('InstanceData'), 'InstanceData'[Category] = "Medium") * 100 +

        CALCULATE(COUNTROWS('InstanceData'), 'InstanceData'[Category] = "Low")

)

Then go to Data view, select InstanceSortTable[Instance], and Sort by Column → SortOrder.

3. Create a Relationship

In Model view, relate:

InstanceSortTable[Instance] → InstanceData[Instance]

4. Create the Chart

Use a Stacked Column Chart:

• X-axis: InstanceSortTable[Instance]
• Y-axis: Count of InstanceData[Instance]
• Legend: InstanceData[Category]

This will sort the X-axis based on:

• Highest Critical count
• Then Medium
• Then Low

Please find the attached pbix file for reference.

Hope this helps.

Thanks!

Inogic