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 ...
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
Helper 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
Community 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!