Dynamic Ranking on Bases of multiple columns(Category and Sub-Category)

[Anonymous]

Hi Team,

we have a requirement to create the Dynamic rank. 

we have a  data like Category and sub-Category and profit. we need to create the dynamic Ranking for Category, Sub-Category over the profit. in the high level category wise it will disply the ranking and we click on the expand button rank should be disply on the subcategory wise ranking. for your refrence attaching the data images.

High levelLow level

Please do needful

Thanks

Koushik

[Anonymous · Accepted solution]

Hi Team

I have written the below code Some how it's working

Rank = IF(ISFILTERED('Order Data'[Product Name]),RANKX(ALL('Order Data'[Product Name]),[Total Profit],,DESC),IF(ISFILTERED('Order Data'[Sub-Category]),RANKX(ALL('Order Data'[Sub-Category]),[Total Profit],,DESC),IF(ISFILTERED('Order Data'[Category]),RANKX(ALL('Order Data'[Category]),[Total Profit],,DESC))))

[mahenkj2]

Hi @Anonymous ,

Please create a measure for Profit:

Profit = 
SUM ( 'Table'[Total profit] )

Then use this measure in final rank formula:

Rank Name Simple = 
IF (
    HASONEVALUE ( 'Table'[Category] ),
    RANKX ( CALCULATETABLE('Table', REMOVEFILTERS('Table'[Sub category])), [Profit] )
)

basically, what we are doing, is removing filters from Sub category with the help of CALCULATETABLE function.

Hope it helps.

[johnt75]

The SQL BI guys have a great article on this at https://www.daxpatterns.com/ranking/ 

[Anonymous]

Hi Johnt,

I'm following the same url but not getting proper result,can you please help me with code.

please find the dax code i was written.

Rank in Category =

VAR SalesAmountCurrentProduct = 'Order Data'[Total Sales]

VAR ProductsInCategory =

CALCULATETABLE (

'Order Data',

REMOVEFILTERS ( 'Order Data'[Sub-Category] ),

ALLSELECTED ( 'Order Data' ),

VALUES ( 'Order Data'[Category] )

)

VAR ProductRank =

IF (

ISINSCOPE ( 'Order Data'[Sub-Category] ),

RANKX (

ProductsInCategory,

'Order Data' [Total Sales]

)

)

VAR Result =

IF (

NOT ISBLANK ( SalesAmountCurrentProduct ),

ProductRank

)

RETURN

getting the results like below

Result

[johnt75]

I don't see any real difference from the code in the article. What is the definition of [Total Sales] ?

[Anonymous]

Total Sales = SUM('Order Data'[Sales])