How to change the ranking based on the values of 2 columns fields in a Matrix table

[roytap]

I have a Matrix table with the following setting

Rows: Customer

Columns: Year

Value: Sales Amount

I set a visual filter based on Top 20 Customer By Sales Amount as shown below;

 

How can i set the ranking based on year 2018?

Currently, the ranking is based on the total value of year 2016 + 2017 + 2018.

Thanks

[Zubair_Muhammad]

@roytap

Try this MEASURE

RANK =
RANKX (
    ALLSELECTED ( TableName[Customer] ),
    CALCULATE ( SUM ( TableName[SalesAmount] ), TableName[Year] = 2018 ),
    ,
    DESC,
    DENSE
)

[roytap]

But how do i sort by using this Rank?

The sort by column is disabled when i highlight this meansure in my query list, 

Thanks.

[Zubair_Muhammad]

@roytap

Are the rankings correct?

Could you show a screenshot? I want to understand what exactly is the sorting problem?

[roytap]

Yes, the ranking seems to be correct, but i am unable to sort it accordingly based on the rank as shown below:

[roytap]

HI,

Any idea how I can sort the Matrix table by year 2018? I am still unable to find any way that it can be sorted by year 2018. By deafult, it will automatically be sorted by the total aggregation of the year value.

[Zubair_Muhammad]

Hi @roytap

I tried it as well..

I am unable to sort in a Matrix too

May be you can use a TABLE visual

[roytap · Accepted solution]

Hi,

Finally, managed to do that using the formula you provided. 

I need to put the Rank field after the Sales value and sort it ascending. Then hide the entire Rank column and it works!

Thanks.