Need to show Top 3 category and Top 3 subcategory when we apply drilldown. Not possible with TOPN

[milankRaj]

For Example, consider the below "Tickets" table which contains different categories and subcategories.

I am trying to show the top 2 categories in the bar chart and drill down by one of the top 2 categories, it should show the top 2 subcategories with the selected category. Please refer to the table and bar visual "Top 2 Category & Sub Category by Tickets" which should get the top 2 categories as well as the top 2 subcategories within the selected category.

As of now, it's not giving the expected output.

DAX :

ISCategory = COUNTROWS('Tickets') = CALCULATE(COUNTROWS('Tickets'),ALL('Tickets'), VALUES('Tickets'[category]))

----

CategoryRank =

IF (

[ISCategory],

RANKX ( ALLSELECTED ( 'Tickets'[category] ), [Ticket Count] ),

RANKX ( ALLSELECTED ( 'Tickets'[subcategory] ), [Ticket Count] )

)

Attaching the sample pbix for reference.

https://drive.google.com/file/d/1nm0fTtVSfmMDX1vZ4yG04VmsAWczyNcN/view?usp=sharing 

[lbendlin]

you're pretty close, but you need to use nested RANKX, not alternating RANKX if you want to show the top two subcategories inside each of the top two categories.

[milankRaj]

Could you help me by sending the the DAX for Nested RANKX with the pbix shared?

[lbendlin]

Here is one implementation

CR := RANKX(ALL(Tickets[category]),CALCULATE(sum(Tickets[number]),all(Tickets[subcategory])))

SCR := RANKX(Filter(ALL(Tickets[category],Tickets[subcategory]),Tickets[category]=max(Tickets[category])),CALCULATE(sum(Tickets[number])))

This will pull the first two categories, and then the first two subcategories for these categories (if you set filters for CR<3 and SCR<3)

Also refer to this explanation: https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures