Ranking based on a distinct count of a particular column ignoring other columns

[SB13]

I have several columns. Column1  contains 'category'. Column2 contains sub category showing yes and no options. Column 3 has sub sub category showing Y and N option. Column 4 has IDs. I want to have top 10 categories based on distinct count of IDs filtered 'yes' for sub category and Y for sub sub category.

There is also a coloumn which shows grades.The table must contain grades column but the top 10 caculation shoould be based on the distinct count of IDs using the filters and not based on grades too. Can somebody please help me with this complex issue. Many thanks

[Anonymous · Accepted solution]

Hi @SB13 ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Create the new column to filter yes for sub category and Y for sub sub category. Add the field to filter pane and set the value is 1.

yes_Y_category = IF(AND('Table'[sub category] = "yes", 'Table'[sub sub category] = "Y"), 1, 0)

3.Create the measure to distinct ID.

Discou_ID = SUMX('Table', DISTINCTCOUNT('Table'[ID]))

4.Create the measure to rank.

Rank_categ = RANKX(ALL('Table'[Category]), [Discou_ID],,DESC,Dense)

5.Drag the measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[Sahir_Maharaj]

Hello @SB13,

Can you please try this approach:

1. Create a Measure for Distinct Count of IDs

DistinctCountFilteredIDs = 
CALCULATE(
    DISTINCTCOUNT(YourTable[ID]), 
    YourTable[SubCategory] = "yes",
    YourTable[SubSubCategory] = "Y"
)

2. Create a Measure for Ranking Categories

RankCategories = 
RANKX(
    ALL(YourTable[Category]), 
    [DistinctCountFilteredIDs], 
    , DESC, Dense
)