Forum Discussion

tuncay's avatar
tuncay
Helper III
4 years ago
Solved

Sort the visualization by month

I have a big list of data, where I have specific data sort by month about an organizational unit. When I create a visual, I try to sort the column month by monthNum, but it says: 

The Month column cannot be sorted by MonthNum. There cannot be more than one value for the same value in Month.

 

Since I have a list, where the months are listed in one column (type: text), it doesn't work. Is there any solution for this?

 

I would try changing the type of Month to date, but I have also a rows in that "Month" column like YTD, or ACT YTD PY. Need ur help, thanks.

 

Need Help
  • FarhanAhmed's avatar
    FarhanAhmed
    4 years ago

    This is because your MonthNum has different values for Same Month 

     

    e.g. Jan has 169,183,197 etc causing issues while sorting

     

    to make this work your each text value must have the same Numeric value for sorting.

     

    Try create a calculated column to give you values 

     

    - Jan =1

    - Feb =2 

     and so on.

     

    Then apply sort by option and it will work for you.

     

    Note: This calculated column should be done in Power Query or in Data Source.

7 Replies

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    4 years ago

    This is because your MonthNum has different values for Same Month 

     

    e.g. Jan has 169,183,197 etc causing issues while sorting

     

    to make this work your each text value must have the same Numeric value for sorting.

     

    Try create a calculated column to give you values 

     

    - Jan =1

    - Feb =2 

     and so on.

     

    Then apply sort by option and it will work for you.

     

    Note: This calculated column should be done in Power Query or in Data Source.

    • tuncay's avatar
      tuncay
      Helper III
      4 years ago

      I tried this:

       

      Spoiler

      if [Month] = "ACT YTD PY" then 1
      else if [Month] = "Jan" then 2
      else if [Month] = "Feb" then 3
      else if [Month] = "Mar" then 4
      else if [Month] = "Apr" then 5
      else if [Month] = "May" then 6
      else if [Month] = "Jun" then 7
      else if [Month] = "Jul" then 8
      else if [Month] = "Aug" then 9
      else if [Month] = "Sep" then 10
      else if [Month] = "Oct" then 11
      else if [Month] = "Nov" then 12
      else if [Month] = "Dec" then 13
      else if [Month] = "YTD" then 14

      else 15

      But it only shows me 1, 15 and 14. It doesn't work so well. Why?

      • FarhanAhmed's avatar
        FarhanAhmed
        Community Champion
        4 years ago

        This should work.

         

        Make sure that you don't have any trailing or leading spaces in your data.

        Try Trim before applying this step.