Top N Within Matrix Table

[mm_engstrom]

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

[miltenburger]

Just click on the column header and it will sort automatically

Kind regards

[mm_engstrom]

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 · Accepted solution]

@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