how to sort a matrix in powerbi by a specific column

[sirgeli]

Hi, how can one get a matrix in powerBI to sort on a specific column?

I want to sort on the latest quarter's rank, the quarter will change over time, so cannot hard code it.

[Anonymous · Accepted solution]

Hi, @sirgeli 

Based on your information, I create a sample table:

Then create a new measure

LatestQuarterRank = 
VAR LatestQuarter = MAX('DateTable'[Quarter])
RETURN
RANKX(
    ALL('Table'[Category]),
    CALCULATE(
        SUM('Table'[Value]),
        'DateTable'[Quarter] = LatestQuarter
    ),
    ,
    DESC,
    DENSE
)

 Add the LatestQuarterRank metric to the matrix visualization. Sort the matrix by measure:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[danextian]

Hi @sirgeli 

The sorting using a measure is based on the total of those columns. So you need to write a measure that returns the latest quarter's rank at the total. For example,

IF(
    NOT(HASONEVALUE('datetable'[quarter])),
    [latest quarter rank],
    [per quarter rank]
)

[danextian]

Below is a sample formula

Latest Quarter Rank =
VAR LatestQuarter =
     --to calculate the latest quarter with value
    CALCULATE ( LASTNONBLANK ( Dates[Quarter], [Total Revenue] ), ALL ( Dates ) )
RETURN
    RANKX (
        --column to rank
        ALL ( Data[Geo] ),
        CALCULATE (
            [Total Revenue],
            FILTER ( ALL ( Dates ), Dates[Quarter] = LatestQuarter )
        ),
        ,
        DESC,
        DENSE
    )

[sirgeli]

sorry, I am very new to this and I think I am doing something wrong, I tried your suggestion, but are getting a bunch of 1's in the rank column and it applies it to all quarters in the table:

my measure:

Latest Quarter Rank =

VAR LatestQuarter =

    CALCULATE ( LASTNONBLANK ( 'ASISA Stats'[Date], [Total AUM] ), ALL ( 'ASISA Stats'[Date] ) )

RETURN

    RANKX (

        ALL ( 'ASISA Stats'[AUM] ),

        CALCULATE (

            [Total AUM],

            FILTER ( ALL ( 'ASISA Stats'[Date] ), 'ASISA Stats'[Date] = LatestQuarter )

        ),

        ,

        DESC,

        DENSE

    )

[sirgeli]

how would I create a measure for latest quarter rank and per quarter rank?

Want to try your solution, but not 100% sure what to do for those two parameters?

[Uzi2019]

Hi @sirgeli 

You can simply sort the matrix table by using sort by fucntionality. follow the screenshot for your guidline.

I hope I answered your question!

Let me know if I am missing something

[sirgeli]

that doesn't work, else I would have done that, it actually sorts it by the total column and not the Sep 2024 rank column