Show only top 5 Categories and top 5 Sub-Categories on Chart

[SamTrott]

In my date I have two fields, one for Category and one for sub-category, I want to have a chart that only show the top 5 Categories and then the top 5 Sub-Categories. In excel it looks like this:

I wrote two measures one to rank Category and one to rank Sub-Category and these work when I just have one Axis but not with both. I think I need some kind of measure that ranks heirarchy but unsure on how to accomplish this.

What do I need to do to get the chart in BI to display similar to the Excel version. 

[amitchandak]

@SamTrott , refer if this can help

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

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[SamTrott]

That may be what I need but I'm having a hard time relating it to mine since the examples deal with numeric values in fields instead of Counting of instances. Still leaves me unsure of how to write the measure to work with my data. 

[v-chuncz-msft]

@SamTrott 

You may also try to create a calculated table first.

[tex628]

Can you post your two measures? 

/ J

[SamTrott]

Measure 1: 

Rank Category by closed incidents = RANKX(ALL(IncidentsClosed[Category]),[Count of Incidents Closed])

Measure 2:

Rank Sub-Category by closed incidents = RANKX(ALL(IncidentsClosed[Sub-Category]),[Count of Incidents Closed])

Both Measures are looking at the respective fields. But I had to put both Fields into a heirarchy to display non Cancatenated labels, not sure if relevant but there you go. 

[tex628]

When you are applying that ALL() to Sub-Category you are removing the main category filter, meaning that you are creating a rank over all subcategories. I think you need to use a selectedvalue() to get the main category, then filter the IncidentsClosed table again. 

Ive got a meeting but ill take a better look at this after! 

/ J