Forum Discussion

tomas789's avatar
tomas789
Regular Visitor
7 years ago
Solved

Sorting Matrix sub column by Year

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?

 

Need Help
Tips and Tricks
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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.

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    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.