Apply Rank to a subcategory when slicers applied

[Suryakala]

I have a fields RC category, under that Application catgeory. It should sort by Defect count. I also have a date slicer. When i select a date range, I want to have a clustered graph, which should display the RC category . Under RC category, there should be top 10 applications shown based on the defect count. I am trying to use the RANKX. But it is not working. Pls help as I am a beginner to POwer BI. I have tried multiple options but it doesnt work.

Rank2 = IF(ISINSCOPE(nonproditrack[Application Fixed In]),(RANKX(ALL(nonproditrack[Application Fixed In]),nonproditrack[NonProdDefectcount],,DESC,Dense)),if(ISINSCOPE(nonproditrack[RC Categorization]),RANKX(ALL(nonproditrack[RC Categorization]),nonproditrack[NonProdDefectcount],,DESC,Dense)))

[v-tangjie-msft · Accepted solution]

Hi @Suryakala ,

Thanks DataNinja777  for the quick reply. I have some other thoughts to add:

(1) This is my test data. 

(2) We can create a measure. 

Rank =
RANKX (
    ALLSELECTED ( 'nonproditrack' ),
    [NonProdDefectcount],
    MAX ( 'nonproditrack'[NonProdDefectcount] ),
    DESC
)

(3) Place [rank<=10] on the visual object's filter and then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[DataNinja777]

Hi @Suryakala ,

To achieve your desired result—showing RC categories with top 10 applications under each category based on Defect Count, while dynamically adjusting to the selected date range—we can optimize your approach using RANKX. Below is a working solution.

Rank_Application = 
VAR CurrentApplication = SELECTEDVALUE(nonproditrack[Application Fixed In])
RETURN
IF (
    ISINSCOPE(nonproditrack[Application Fixed In]),
    RANKX(
        FILTER(
            ALL(nonproditrack[Application Fixed In], nonproditrack[RC Categorization]),
            nonproditrack[RC Categorization] = SELECTEDVALUE(nonproditrack[RC Categorization])
        ),
        CALCULATE(SUM(nonproditrack[NonProdDefectcount])),
        ,
        DESC,
        DENSE
    )
)

Best regards,

[Suryakala]

@DataNinja777 @v-tangjie-msft Thank you Guys for your quick reply! Sorry for my delayed response.

I would like to explain my query in detail.

@v-tangjie-msft 

1. Slicer is only on the date. 

2. In th graph, i want to display both the categories.

3. I want to sort the the sub category alone based on the defectcount under each category.

4. Catgeory should appear only once. (When i try to sort by sub actegory, multiple categories are being created)

Below is sample graph from PPT,

In PowerBI, i am not able to categorize the apps based on defect count. Pls help as I have been struggling to achieve this sort within subcategory when category is also present in graph.