Forum Discussion

MarcUrdang's avatar
MarcUrdang
Icon for Post Patron rankPost Patron
6 years ago
Solved

Sorting in a stacked bar chart

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

Need Help

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      MarcUrdang
      Icon for Post Patron rankPost Patron
      6 years ago

      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's avatar
        MarcUrdang
        Icon for Post Patron rankPost Patron
        6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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/300778

     

    If this helps please kudo.

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

    • MarcUrdang's avatar
      MarcUrdang
      Icon for Post Patron rankPost Patron
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.