Forum Discussion

Lio123's avatar
Lio123
Advocate I
1 year ago
Solved

Help with sorting the matrix

Dear developers, 

I need your help to sort the matrix visuals based on months, along with months I have some other values as well in the columns, you can put them at last after months. 

Please support.

Thanks

 

Need Help
  • Lio123's avatar
    Lio123
    1 year ago

    Hi wardy912  & mdaatifraza5556 

    Thank you for your reponse,

    I have date dim table and month sort column as well,.

    The issue was with other values apart from month values, so I just added the custom sorting column in PQ and then sorted the month column in the report view and it worked. 

    Regards

4 Replies

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago

    Hi Lio123 


    Did you try creating a Month Number column in the Date table and then sorting the Month Name column by that Month Number?

    Follow to get it 

    Create Month No column for sorting 


     

     

    Click on month name column and then sort by column from the ribbon and select month no.

     

     

     

     

    Result

     

     




    If this answers your questions, kindly accept it as a solution and give kudos.

    • Lio123's avatar
      Lio123
      Advocate I
      1 year ago

      Hi wardy912  & mdaatifraza5556 

      Thank you for your reponse,

      I have date dim table and month sort column as well,.

      The issue was with other values apart from month values, so I just added the custom sorting column in PQ and then sorted the month column in the report view and it worked. 

      Regards

      • wardy912's avatar
        wardy912
        Super User
        1 year ago

        Glad the issue is resolved, please help others by marking as solved.

        Thanks!

  • wardy912's avatar
    wardy912
    Super User
    1 year ago

    Hi Lio123 

     

     It's best practice to add a date table and relate to your fact table date, here's one I made earlier!

     

    Date = 
ADDCOLUMNS (
    CALENDAR (
        MIN ( [Date column from existing table] ),
        MAX ( [Date column from existing table] )
    ),
    "MonthNo", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "MMMM" ),
    "MonthYear", FORMAT ( [Date], "MMMM YYYY" ),
    "MonthYearShort", FORMAT ( [Date], "MMM YY" ),
    "MonthYearNo", FORMAT ( [Date], "YYYYMM" ),
    "Quarter", QUARTER ( [Date] ),
    "Year", YEAR ( [Date] ),
    "Day", DAY ( [Date] ),
    "WeekNumber", WEEKNUM ( [Date] ),
    "WeekdayNum", WEEKDAY ( [Date] ),
    "WeekdayName", FORMAT ( [Date], "DDDD" ),
    "PreviousWeek", WEEKNUM ( [Date] ) -1 ,
    "WeekStartDate", ([Date] - WEEKDAY ( [Date] , 1 ) +1),
    "WeekEndDate", ([Date] - WEEKDAY ( [Date] , 1 ) +7),
    "YearMonth", FORMAT ( [Date], "YYYY-M" )
    )

     

    You can then sort the columns by my sorting columns (e.g. sort MonthName by MonthNo).

    Go to the table view and select 'sort by column' from the 'column tools' toolbar.

     

     

    That will show the months in the right order.

     

    You could add a sort column to your fact table, but a date table is definitely preferable.

     

    I hope this helps, please give a thumbs up and mark as solved if it does, thanks!