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

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