Using TOP N filter with a measure that ranks upper level of hiearchy ?

[Anonymous]

Hello Everyone,

Suppose we have a table and subcategory names, and let's say a measure [total sales] and in the subcategory name filter

we have TOP N filter and a measure in it and it's ranking in ascending order.

They want to keep subcategory names and the measure in the table [total sales] and they want to change the TOP N filter (measure) to rank in CATEGORY level... 

first question, is it possible ? because I have a measure 

    IF(

        HASONEVALUE('Table'[CategoryName]),

        RANKX (

            ALL('Table'[CategoryName], 'Table'[CategoryName] ),

            [total sales] , , DESC

        )

    )

and it's ranking WHEN I use only category name field and [total sales] measure in the table.
because they want to keep subcategory field... I think just because of that ? I can't bring the result for Category

because we have subcategory field in the measure... 

IF It's possible, please help me out....

[Anonymous]

 @amitchandak I think the scenario is different than you assume. I shared a picture above
Expecting your help. Thanks

[Anonymous]

[amitchandak]

@Anonymous , Can you share some example in table format, not able to get it.

Please check if these two can help

Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA

2 column top N = CALCULATE([Net], TOPN(10, SUMMARIZE(ALLSELECTED('Item'), 'Item'[Brand], 'Item'[Category]), [Net],DESC),VALUES('Item'[Brand]), VALUES('Item'[Category]) )

[Anonymous]

@amitchandak 

Category and Subcategory are coming from different tables and there is no like nested hiearchy but 
when you have subcategory in the table it shows what category are in right. 

Unfortunately I can't share a sample data but I can tell anything if you have any questions. Your solution above It's great but it's working for nested hiearchy. I believe we are so close to solve this issue. 

Please don't hesitate to ask any questions and finalize this issue maybe it will help others because this is a bit specific.

thanks

[amitchandak]

@Anonymous , Have you tried this

Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA

Rankx(summarize(allselected(Fact), Dim1[Sub Category], Dim2[Category]), Dim2[Category] = max(Dim2[Category]) , [measure],,desc, dense)

or

Rankx(summarize(allselected(Fact), Dim1[Sub Category], Dim2[Category]), Dim1[Sub Category] = max(Dim1[Sub Category]) , [measure],,desc, dense)

[Anonymous]

It looks clean and neat but I didn't work on my situation. Thanks anyway.

[amitchandak]

@Anonymous , You have options like

RANKX (
ALL('Table'[CategoryName], 'Table'[SubCategoryName] ),
[total sales] , , DESC
)

or

RANKX (
filter(ALL('Table'[CategoryName], 'Table'[SubCategoryName] ),'Table'[CategoryName] = max('Table'[CategoryName]) ),
[total sales] , , DESC
)

Also refer

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/