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

 

RankTerritory numberBF3.8BF5.0BF 5.8Total
11111105520
2111255515

 

The closest format i can think of is to use Matrix table. The problem is that Rankx can only be placed as VALUES...so here is what i have now below. 

 

Question 1) any way to move the ranking to the row? so before the territory number column?

Question 2) how to get rid of ranking for each sub product category. i only want to know $$, dont really care about how they rank individually. i do want to keep the overall ranking though

 

 

thank you all!!

 

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!

9 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi leilei787 

    Best to create a new calculated column to rank your products. Then you can just drag it to rows or otherwise use the following measure which will show blanks for any higher granularity. 

    IF (

        HASONEVALUE ( Producr Key ),

        VALUES ( "RANKX calculation" )

    )

    If you just want to to hide the subcategories just resize the column using the mouse to zero width. 

    it would be great if you can share a sample file to assesst further. 

    • leilei787's avatar
      leilei787
      Helper II
      4 years ago

      thank you! somehow i could not get it work even i use calculated column. here is what i did

       

      i created a calculated column, here is my formula: 

       

      Bflex TM Ranking3 = rankx(ALLSELECTED('vw_fact_ShippingDetailWithReturns (Commercial)'[PrimaryGeoCode_c]),[Total Shipping Net])
       
      i am able to put the rank on the ROWs....however, in the matrix format, it is still not working right
       

       

      here are the sample data:

       

      so ideally i want to have the rank first, then territory numbers, product family on the column. Rank by total sales, not individual product. 

       

      PrimaryGeoCode_cCurrencyCodeShippedUSDAmountNetProductFamilyCode
      1101USD2950BF3.8
      1101USD1475BF3.8
      1101USD2595BF3.8
      1101USD1475BF5.0
      1105USD1297.5BF3.8
      1105USD1297.5BF5.0
      1105USD1450BF5.8
      1105USD1297.5BF3.8
      1105USD1297.5BF5.0
      1110USD1450BF5.8
      1110USD1297.5BF3.8
      1110USD1297.5BF5.0
      1110USD1450BF5.8
      1110USD1700BF5.8
      1110USD1700BF5.8
      1110USD1475BF5.0
      1106USD5454BF5.0
      1106USD24353BF5.8
      1106USD3553BF3.8
      1106USD643BF5.0
      1106USD356BF5.8
      1106USD665BF5.8
      • tamerj1's avatar
        tamerj1
        Community Champion
        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
    Community 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
      Helper 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
        Community 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.