Forum Discussion

jstanten's avatar
jstanten
New Member
3 months ago
Solved

Dual Sorting for Stacked Bar Chart

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?

Tips and Tricks
  • cengizhanarslan's avatar
    cengizhanarslan
    3 months ago

    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.

5 Replies

  • DanieleUgoCopp's avatar
    DanieleUgoCopp
    Super User
    3 months ago

    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

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    3 months ago

    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.

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 months ago

    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?

  • v-achippa's avatar
    v-achippa
    Community Support
    3 months ago

    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

  • jstanten's avatar
    jstanten
    New Member
    3 months ago

    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