topic Re: Using TOP N filter with a measure that ranks upper level of hiearchy ? in DAX Commands and Tips

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

Anonymous — Wed, 12 Oct 2022 19:37:04 GMT

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....

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

amitchandak — Thu, 13 Oct 2022 01:33:40 GMT

Anonymous , You have options like

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

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/

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

Anonymous — Thu, 13 Oct 2022 03:18:58 GMT

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

Anonymous — Thu, 13 Oct 2022 03:20:09 GMT

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

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

amitchandak — Thu, 13 Oct 2022 03:22:54 GMT

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]) )

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

Anonymous — Thu, 13 Oct 2022 14:01:58 GMT

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

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

amitchandak — Fri, 14 Oct 2022 02:03:09 GMT

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)

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

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

Anonymous — Mon, 17 Oct 2022 14:40:23 GMT

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