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

[SandeepSathya]

Hello, 
I need help with sorting the chart based on the value count. 

Sample data:

Instance	Category
Instance 1	Low
Instance 1	Medium
Instance 2	Medium
Instance 2	Critical
Instance 2	Critical
Instance 2	Critical
Instance 3	Low
Instance 3	Medium
Instance 3	Medium
Instance 3	Low
Instance 3	Critical
Instance 1	Critical

Based on the data I have following chart created:

As expected, defaut sort is based on Total count (descending). 

My requirement is that: X axis sort order should be based on the logic : 

Instance with highest # of critical should be left most.
Logic: first compare # of critical for the Instance, if same then # of medium, if same then # of low. (not based on the total # under each instance)

So based on my data I want the graph to be sorted in following order:

First vertical should be Instance 2 as it has highest # of critical (value 3)

Second vertical should be Instance 3 (has same # of critical as Instance 1, but # of Medium for Instance 3 is higher than Instance 1)

Third vertical should be Instance 1.

Any suggestion on how to sort X axis based on custom rule for the 'Count of legend' would be of great help! Thanks in advance

[SamInogic]

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

[Palak_Agarwal]

Hello @SandeepSathya 
Power BI doesn’t let us sort the X‑axis directly by values in the legend, but I’ve created a handy workaround:
Create 3 measures to get the count of each category using CALCULATE() for eg:
CALCULATE(COUNT('Table'[Instance]),'Table'[Category] = "Critical")
Similarly calculate the count of Medium and Low.

Then I created a measure for sorting in which I multiplied the critical count with a large constant number to make the count large (I have used 10000 but you can use any value based on the highest value in your data). Then I multiplied the count of medium with a constant smaller than before so that the value is smaller than the count of critical and left count of low as it is, then added all three.
This makes the values weighted with critical having the highest weight and low having the lowest. Refer to the image below:



Then add this measure in the tooltip field and then sort the axis by this sort measure by clicking on the 3 dots in the upper right corner of the chart and select sorting measure in the sort axis.

If this post helps, please accept this as a solution. Appreciate your kudos.

Regards,

Palak