Forum Discussion

james_wood's avatar
james_wood
Frequent Visitor
4 years ago
Solved

How do I sort columns chronologically in stacked column chart?

I have the following stacked column chart. January is the only month for 2022, all others are for 2021. Let's label each column A, B, C, D for confidential purposes. Hopefully you can see that January is the bottom stack for each column however I want this to be the most recent stack as it's the latest addition for 2022. The legend itself only displays the month and not the month and year as I want it to. How do I include both for clarity that January is 2022?

 

Need Help
  • Pragati11's avatar
    Pragati11
    4 years ago

    Hi james_wood ,

     

    What you can do is create a MONTH-YEAR column in Power BI using your DATE column.

    Check this article on how to get this Month-Year column:

    https://radacad.com/generate-year-and-month-combinations-in-power-bi-report-using-dax

     

    Next I created stacked bar chart with the following information:

     

    You can see that at the top stack I see the one for Jan-2019, though I want the latest Month as the top stack which is Mar-2019.

     

    So just sort your legend as shown below:

    So when I did a sort on my LEGEND, I can see the latest month-year appearing as the top stack on the chart.

     

    Let me know if this helps.

     

    Thanks,

    Pragati

6 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    4 years ago

    Hi james_wood ,

     

    What you can do is create a MONTH-YEAR column in Power BI using your DATE column.

    Check this article on how to get this Month-Year column:

    https://radacad.com/generate-year-and-month-combinations-in-power-bi-report-using-dax

     

    Next I created stacked bar chart with the following information:

     

    You can see that at the top stack I see the one for Jan-2019, though I want the latest Month as the top stack which is Mar-2019.

     

    So just sort your legend as shown below:

    So when I did a sort on my LEGEND, I can see the latest month-year appearing as the top stack on the chart.

     

    Let me know if this helps.

     

    Thanks,

    Pragati

    • james_wood's avatar
      james_wood
      Frequent Visitor
      4 years ago

      Thanks for the quick response. I followed your instructions but I don't get a 'sort legend' option when clicking on the three dots. See below (not clear as PowerbI always shows a messed up version of the menu but hopefully you can see the options):

       

       

      It's also arranging the months by alphabetical order and messed up the colours:

       

      Any idea?

       

      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User
        4 years ago

        Hi james_wood ,

         

        Few questions:

        • Is this a stacked bar chart like I used above?
        • Have you moved MonthYear column to the LEGENDD section of the chart visual?
        • Did you went through the whole article I shared above on creating month-year from date column and then further  giving them a right sort order? 

           

        Also, I don't see the messy menu layout like you see at your end. I trust you are using the latest version of Power BI Desktop.

         

        Thanks,

        Pragati