Sorting Matrix sub column by Year

[tomas789]

I'm trying to build a matrix that will has these requirements:

1.  Each year should be a sub column under Sales and ASP.  

For example

|Sales                 | Qty                   |

|2017|2018|2019|2017|2018|2019|

2.  The rows are comprised of Entity, State, Store

ie.  -Entity

       -State

         -Store 

3.  I need the ability to sort the total sales & quantity by each year.  

So far I've successfully built requirements #1 and #2.  However I'm unable to sort based on the year.  Any tricks for this?

[v-yuta-msft · Accepted solution]

@tomas789 ,

You can create a calculate column using DAX like pattern below:

Rank =
RANKX (
    FILTER (
        Table,
        Table[Category] = EARLIER ( Table[Category] )
            && Table[Qty] = EARLIER ( Table[Qty] )
    ),
    Table[sales ],
    ,
    DESC,
    DENSE
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.