Dual Sorting for Stacked Bar Chart

[jstanten]

I have a stacked bar chart with the x axis sorted by date, in ascending order. However, i want the stacked bar chart legend to be sorted by value, not by alphabetical order. There seems to be an out of box limitation to sorting an axis by date while sorting a legend by value. This limitation is inexplicable, but that just seems to be the way it is.

I've tried using the "sort by column" feature in Column Tools, but results in a circular dependency.

To avoid that error I've also tried creating a new "sort table", adding a relationship to my primary fact table, and the using the "sort by column" feature to work around the circular dependency error. No luck.

Is there really no viable path for this?

[jstanten]

Thank you all for your replies! I appreciate the suggestions and support. While this doesn't directly resolve the challenge, it gives me a number of usable work-arounds that I think our team will find helpful.

Thank you all very much!

Justin

[v-achippa]

Hi @jstanten,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @ryan_mayu, @DanieleUgoCopp and @cengizhanarslan for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the responses provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[cengizhanarslan · Accepted solution]

There is no native workaround that fully solves this. You could create a calculated column or Power Query column that assigns a sort rank based on total value across all dates:

LegendRank =
RANKX (
    ALL ( 'Table'[LegendField] ),
    CALCULATE ( SUM ( 'Table'[Value] ) ),
    ,
    DESC,
    DENSE
)

Then sort your legend field by this LegendRank column via Column tools → Sort by column. This will not be dynamic per date (it sorts by overall total), but it gets the highest-value categories to the top of the stack consistently.

[DanieleUgoCopp]

Hello,
If the proejct permit it, you can create a legend with another visual, like a table, and try to link it to the chart, if you want to make disappear something with filter or whatver is needed,
or creating a ranking column in the model for a fixed order,
or to use a custom visual downloading some visual exterally
Best Regards,
Daniele

[ryan_mayu]

@jstanten 

If you want to sort by calcaulated column, that may cause  circular dependency error. You have to do that in Power Query

Could you pls provide some sample data and expected output?