Sorting in a stacked bar chart

[MarcUrdang]

Hi .. I have a stacked bar chart where I sort the x axis by date which works fine. However each bar is stacked by a value per branch. How do I sort each column by the highest value at the bottom up to the lowest at the top. As far as I have understood from research .. this is not possible which doesn't make sense. But I am probably missing something.

thanks

Marc

[Anonymous]

Hi @MarcUrdang ,

You can use RANKX() function to create a measure to sort specific column.

Please check the following steps.

1# Create measures as below.

SUM = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[type]="x"))

Measure = RANKX(ALLEXCEPT('Table','Table'[type]),[SUM])

2# Add the measure to Tooltips field and sort the visual by the measure.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[MarcUrdang]

Hi .. I don't think I am understanding the steps:

I have a value and a category:  

   category = branch names or BranchRelated

   value = Nashua Page Count

I have created measure #1:  but not sure why I have to filter on only ONE of the branches? I probably have this incorrect

Your solution = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[type]="x"))

My solution =    CALCULATE(SUM(Nashua[Nashua Page Count]),FILTER(Nashua,Nashua[BranchRelated]="CPT"))

I tried to create measure #2:  not working 

Your solution = Measure = RANKX(ALLEXCEPT('Table','Table'[type]),[SUM])

My solution = from Calculae onwards its underlined in RED

RANKX(ALLEXCEPT(Nashua,Nashua[BranchRelated]),[CALCULATE(SUM(Nashua[Nashua Page Count]),FILTER(Nashua,Nashua[BranchRelated]="CPT"] 

[MarcUrdang]

[MarcUrdang]

Hi again .. I don't mean to be rude as I know you have helped me so much thus far .. but as I am now under immense pressure to get this right .. I need to repost so hopefully someone can help me in the next 30 minutes or so.

thanks for your help thus far.

regards,
Marc

[Anonymous]

Hi @MarcUrdang ,

I'm not sure if i understand you correctly, I thought you want to sort the visual by one of the types in a same column but it turns out that you want sort the visual by multiple columns? If so you may check the topic below.

https://community.powerbi.com/t5/Desktop/Sorting-by-multiple-columns/m-p/536884#M251918.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[MarcUrdang]

Hi Jay

thanks for coming back to me. Ok let me explain again .. sorry for the confusion.

the bar graph must be sorted along the x axis by date .. works fine

however when trying to sort each bar which is segmented by BRANCH from biggest to smallest based on VALUE the error message says it can't sort by two different fields?

[Anonymous · Accepted solution]

Hi @MarcUrdang ,

So assume the picture below is raw visual.

You want the visual sort by legend i.e show as 20200201 c, b, a; 20200102 c, a, b; 20200103 a, b, c; etc... Am i right?

In that case i'm afriad this can't be done in Power BI currently.

About this feature, there are several ideas have been submit , you can vote for them.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/31937392-legend-manual-sorting.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/37921429-sort-order-legend.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17547193-if-i-can-sort-the-stacke...

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Hello @Anonymous,

Is it still not possible to do this kind of sorting with PowerBI ?

Thanks in advance for your response 🙂 

[MarcUrdang]

I actually now got the calc to work ... but not the sorting 

[Tad17]

Hey @MarcUrdang 

You are wanting to sort the "Colors" in the individual bars (columns) in the visual correct?

If you are simply wanting to change the order of the axis you can click the "..." in the corner of the visual and click "Sort by".

Also try the solution in this link to see if ti answers your question: https://community.powerbi.com/t5/Desktop/How-to-sort-stacked-column-chart-in-Power-BI-desktop/td-p/3...

If this helps please kudo.

If this solves your problem please accept it as a solution.

[MarcUrdang]

Hi 

Yes its already sorted by date on the x axis .. I want a secondary sort in each column based on a criteria ie volume, count etc 

[Anonymous]

I realize this question is a year old, but I found it while looking for a solution to a similar problem, and I think I might have the solution to THIS problem...so I'm sharing it for posterity.  🙂

I think the best solution for your situation would be this: instead of creating one visual with multiple stacked bars, create several visuals with one stacked bar each.  You can sort each bar by the volume or count or whatever your key criterion is, then manually place the bars from left to right in order of date.  You can even insert shapes and text boxes to imitate the look of the shared x-axis that you would get if you'd stuck with a multiple-stacked-bar visual.

This isn't a perfect solution, especially if the report will be used continuously for a long time while new time periods (quarters, for example, if each bar represents a quarter) pass.  If that's the case, you would have to manually add a new stacked bar visual for each new time period as it occurs, which is obviously not ideal.  But if either a) you can't figure out another way to do this and it's SUPER important to achieve this by-bar sort, or b) the report will only be used for a limited amount of time and doesn't need to update and add new bars as time passes, this could be a perfectly workable (if inelegant) solution.