Forum Discussion

mm_engstrom's avatar
mm_engstrom
New Member
8 years ago
Solved

Top N Within Matrix Table

Hi,

I have 3 columns of Data (Top 20 customers for 3 years). I would like to sort the table so it starts with the largest customer in the most recent year. Right now it seems like the 20 custoemrs are just presented randomly.

Thanks in advance

Michael

Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    8 years ago

    mm_engstrom,

     

    Currently, we can not sort your table by using two different column. To work around this issue, you could create another column.

    Sample data

    And then create a table bu using the DAX below.

    Table =
ADDCOLUMNS (
    TOPN ( 20, Table1, Table1[Amount], DESC ),
    "rank", RANKX ( Table1, Table1[Year] * 10000 + Table1[Amount] / 10000,, DESC )
)



    Regards,

    Charlie Liao

3 Replies

  • miltenburger's avatar
    miltenburger
    Helper V
    8 years ago

    Just click on the column header and it will sort automatically

     

    Kind regards

    • mm_engstrom's avatar
      mm_engstrom
      New Member
      8 years ago

      Yes. That will work for the total column for the 3 years, but the total coulm is hidden. I would like to sort on the most recent year - not the total. Nothing happens when clicking on one of the coumns.

      • v-caliao-msft's avatar
        v-caliao-msft
        Microsoft Employee
        8 years ago

        mm_engstrom,

         

        Currently, we can not sort your table by using two different column. To work around this issue, you could create another column.

        Sample data

        And then create a table bu using the DAX below.

        Table =
ADDCOLUMNS (
    TOPN ( 20, Table1, Table1[Amount], DESC ),
    "rank", RANKX ( Table1, Table1[Year] * 10000 + Table1[Amount] / 10000,, DESC )
)



        Regards,

        Charlie Liao