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!

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
    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi leilei787 
      In this case, it has to be a measure that is your first solution. Again we will have the problems of your original query hence back to square 1.
      So I would advise to go back to your original measure and try to manually hide the ranking for subtotals by minimizing the respective column size to zero width with your mouse. Shifting the row grand total to the left is not possible. 
      Finally, you can filter out all product families other than "BF" by creating a new table using simple code

       

      Table 2 = 
    FILTER (
        'Table',
        CONTAINSSTRING ( 'Table'[ProductFamilyCode], "BF" )
    )

       

      Then use the new table instead for your analysis.


      Please let me know if anything further I can help you with.
      Have a great day!