Forum Discussion

leilei787's avatar
leilei787
Helper II
4 years ago
Solved

Question about Rankx in Matrix table format

Hello   i am trying to use Rankx and matrix table to setup a format in BPI like this   Rank Territory number BF3.8 BF5.0 BF 5.8 Total 1 1111 10 5 5 20 2 1112 5 5 5 15 ...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi leilei787 
    Here is the sample file with the solution https://www.dropbox.com/t/ic5uywEC976ZR0zm

    You need to create two columns. 

    Total Amount = 
    VAR CurrentCode = 'Table'[PrimaryGeoCode_c]
    VAR CurrentCodeTable =
        FILTER (
            'Table',
            'Table'[PrimaryGeoCode_c] = CurrentCode
        )
    VAR Results =
        SUMX (
            CurrentCodeTable,
            'Table'[ShippedUSDAmountNet]
        )
    RETURN
        Results 
    Ranking = 
        RANKX (
            'Table',
            'Table'[Total Amount],
            , 
            DESC, 
            Dense 
        )

    Then create your measure

    Shipping Net = 
        SUM ('Table'[ShippedUSDAmountNet] )

    I hope this satisfies your requirement. If so please consider marking this reply as "Accepted" solution. Thank you!