Repeat rank value through hierarchy levels

[JoseCamp]

Hello community, 

I have a product table with different sizes and locations (Table on the left). I have created a ranking for this table based on Product name total sales (Table on the right)

I can't figure out how to replicate the ranking value from the right table, through all the rows that have the same product name, as shown in the Ranking # on the yellow table. I need to to this so I can filter a set of information by ranking (Example the top 2), and be able to display the product information by size and location.

Any idead how to achieve this?

Thanks in advance! 

[JoseCamp]

@AntrikshSharma thanks for your answer! I should have been a bit more specific about my request! (My bad) The product table has selling dates and I need the ranking to change based on the sales of a specific date range, or if a selection of a specific location (s), or a group (s) of products.

Product name	Size	Location	Sells	Date
Prod1	S1	Loc1	$ 10.0	2021-03-10
Prod1	S2	Loc1	$ 20.0	2021-03-10
Prod1	S1	Loc3	$ 30.0	2021-03-12
Prod1	S1	Loc4	$ 20.0	2021-03-09
Prod2	S3	Loc1	$   5.0	2021-03-10
Prod2	S4	Loc4	$   8.0	2021-03-10
Prod3	S5	Loc2	$   4.0	2021-03-10
Prod3	S5	Loc3	$   3.0	2021-03-09
Prod3	S5	Loc4	$   9.0	2021-03-09

Thanks for your help!

[AntrikshSharma · Accepted solution]

@JoseCamp  The file is attached below my signature:

Product Rank =
IF (
    ISINSCOPE ( Jose[Product name] ),
    VAR CurrentProduct =
        SELECTEDVALUE ( Jose[Product Name] )
    VAR TotalSellAtCurrentProduct =
        CALCULATE (
            [Total Sell],
            Jose[Product name] = CurrentProduct,
            ALLEXCEPT ( Jose, Jose[Date] ),
            ALLSELECTED ( Jose[Date] )
        )
    VAR TotalSellEachProduct =
        CALCULATETABLE (
            ADDCOLUMNS ( VALUES ( Jose[Product Name] ), "@Total Sells", [Total Sell] ),
            ALLEXCEPT ( Jose, Jose[Date] ),
            ALLSELECTED ( Jose[Date] )
        )
    VAR RankCurrentSell =
        FILTER ( TotalSellEachProduct, [@Total Sells] >= TotalSellAtCurrentProduct )
    VAR Result =
        COUNTROWS ( RankCurrentSell )
    RETURN
        Result
)

[AntrikshSharma]

@JoseCamp  Try this:

Product Rank = 
VAR CurrentProduct = Jose[Product Name]
VAR TotalSellAtCurrentProduct =
    CALCULATE ( SUM ( Jose[Sells] ), ALLEXCEPT ( Jose, Jose[Product Name] ) )
VAR TotalSellEachProduct =
    ADDCOLUMNS (
        VALUES ( Jose[Product Name] ),
        "@Total Sells", CALCULATE ( SUM ( Jose[Sells] ), ALLEXCEPT ( Jose, Jose[Product Name] ) )
    )
VAR RankCurrentSell =
    FILTER ( TotalSellEachProduct, [@Total Sells] >= TotalSellAtCurrentProduct )
VAR Result =
    COUNTROWS ( RankCurrentSell )
RETURN
    Result