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 a...
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

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