Forum Discussion

leilei787's avatar
leilei787
Icon for Helper II rankHelper 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 ...
RANKX issue
  • 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!

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi leilei787 
A slicer will do. But to make it practical I created a new column which I called "Product Category" that returns "BF" for any family code that contains "BF". Code can be adjusted as per your requirement

Product Category = 
    IF (
        CONTAINSSTRING ( 'Table'[ProductFamilyCode], "BF" ),
        "BF",
        'Table'[ProductFamilyCode]
    )

Now your report looks like this

You can use the same link to re-download the file.