Sorting a matrix with a field hidden inside the measure

[PBI_SUPERUSER]

Hi,

I have created a matrix with a as below.

Answer is a mesure which is consists of several other measures.

Answer =

var metric =

SWITCH( SELECTEDVALUE(Driver[ID]),

1, Table[Y-6],

2,Table[Y-5],

3,Table[Y-4],

4,Table[Y-3],

5,Table[Y-2],

6,Table[Y-1],

7,Table[FY24],

8,Table[FY25],

9,Table[Highest Ever Sales in Last 5 Years],

10,Table[AverageSales_in_Last 5 Years],

11,Table[AverageSales_in_Last 12 Months],

12,Table[AverageSales_in_Last 3 Months],

13,Table[FY25MTD])

RETURN

metric

I need to sort the matrix by [FY24] from highest to lowest.But sort option in matrix visulization only show [answer] .is there a way to do this.

[Anonymous · Accepted solution]

Hi  @PBI_SUPERUSER ,

Try to modify your formula like below:

answer =
VAR metric =
    SWITCH (
        SELECTEDVALUE ( 'Table'[DriverID] ),
        1, [Table_Y1],
        2, [Table_Y2],
        3, [Table_Y3]
    )
RETURN
    IF (
        ISFILTERED ( 'Table (2)'[Y-1] ),
        CALCULATE (
            metric,
            FILTER ( ALLSELECTED ( 'Table (2)' ), NOT ISBLANK ( [Y-1] ) ),
            ALLSELECTED ( 'Table (2)' )
        ),
        metric
    )

Best Regards,
Adamk Kong

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