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.

  • leilei787's avatar
    leilei787
    Icon for Helper II rankHelper II
    4 years ago

    hi Tamerj1, one more question, your previous solution works beautifully! thank you!

     

    the only issue now i have is that the ranking works, let's say if i look at 2021 as a whole. but if i select, Jan or March, the ranking wont start from 1,2,3....i think the reason is because the "Total Amount" formula is looking at acumulated amount of what each territory sold. if i select an individual month, ranking remains the same....is there a way to re-rank if i select individual month?

     

    PrimaryGeoCode_cCurrencyCodeShippedUSDAmountNetProductFamilyCodeMonth
    1101USD2950BF3.8Jan
    1101USD1475BF3.8Jan
    1101USD2595BF3.8Feb
    1101USD1475BF5.0Apr
    1105USD1297.5BF3.8March
    1105USD1297.5BF5.0Feb
    1105USD1450BF5.8Apr
    1105USD1297.5BF3.8Jan
    1105USD1297.5BF5.0March
    1110USD1450BF5.8Jan
    1110USD1297.5BF3.8Jan
    1110USD1297.5BF5.0Apr
    1110USD1450BF5.8Feb
    1110USD1700BF5.8March
    1110USD1700BF5.8Feb
    1110USD1475BF5.0Feb
    1106USD5454BF5.0Apr
    1106USD24353BF5.8May
    1106USD3553BF3.8May
    1106USD643BF5.0May
    1106USD356BF5.8Apr
    1101USD3545BF5.8Jan
    1101USD3434BF5.8Jan
    1101USD33BF5.8March
    1101USD2345BF5.0March
    1105USD24334BF5.0March
    1105USD235BF5.8March
    1105USD33BF3.8Apr
    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi leilei787 
      Would you help me out please I'm a little confused.
      Do you need to consider other product families in the ranking?
      "OR"
      Do you mean we need to filter out all records that belongs to product families other than BF families. Then do the ranking?

      Will the ranking be based on ever total amount, anual total amount or monthly total amount? As this is a calculated column and we have to clearly understand at which granularity our code shall be based on.
      Based on the current code, when you select a month you will see the amounts aggregated by month but ranking will not change unless you updated your source data.

      If you can share your sample file updated with more data and more rates that would be great.

      • leilei787's avatar
        leilei787
        Icon for Helper II rankHelper II
        4 years ago

        Hi Tamerj1

         

        sorry for the confusion. below is a more realistic sample.

         

        the goal is to rank territory that sold BF product ( And BF product only. i need to show BF3.0, 5.0 and 5.8 but no need to rank these sub-categories, rank still based on the total). so previous ask was still valid. now the problem is that the ranking also need to be dynamic if i select an individual month. For example, if i select Feb, the rank will be based on BF sales in Feb. If i select Jan to April, the rank will be based on BF sales from Jan to Apr....is it possible?

         

        PrimaryGeoCode_cCurrencyCode ShippedUSDAmountNet ProductFamilyCodeMonth
        1101USD $                        34,342GVM1
        1101USD $                          5,332BF2.52
        1101USD $                             355BF3.82
        1101USD $                          3,556BBQ3
        1101USD $                        24,522BF5.04
        1102USD $                          3,456VGN1
        1102USD $                          7,445BF5.02
        1102USD $                             844BF3.82
        1102USD $                             985GVM3
        1102USD $                          8,980BF3.84
        1103USD $                             593VGN1
        1103USD $                          4,653BF3.82
        1103USD $                          3,353BF5.02
        1103USD $                          2,432GVM3
        1103USD $                          2,556BF3.84
        1104USD $                          4,342BF5.01
        1104USD $                        56,245BF3.82
        1104USD $                          9,543VGN2
        1104USD $                        29,405BBQ3
        1104USD $                        34,532BF5.04