RANKX: using aggregate functions on the expression

[bleow]

I have the following data on ID and Type:

ID	Type	Type TopN	Ranking
1	A	A	1
2	B	B	2
3	C	Others	3
4	A	A	1
5	A	A	1
6	B	B	2
7	C	Others	3
8	D	Others	4
9	E	Others	5

and I want to add the Type TopN column that shows the type for the top N types by count, and Others for the rest.

To do this, I tried adding a Ranking column that (theoretically) ranks the type by frequency of appearance, i.e. since A appears 3 times it has a ranking of 1, B ranking of 2 etc. From there, I can use the following code:

RETURN IF(
    Ranking<=3, 
    [Type], 
    "OTHERS"
)

To get Type TopN column.

The issue comes with trying to get the Ranking column. I tried:

RANKX(
    table,
    [Type],
    COUNT(table[Type]),
    DESC,
    Dense
)

but it gives me the error "Function 'RANKX' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values."

I know my requirement is possible to achieve by duplicating the table and using Group By to aggregate the count, apply the RANKX filter, then do a left join, but I'm wondering if RANKX can do this in one step and if there's anything I'm not understanding about this function. Thanks

[v-angzheng-msft · Accepted solution]

Hi, @bleow 

To create 2 column with this:

_RankColumn =
RANKX (
    'Table',
    CALCULATE ( COUNT ( [Type] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ),
    ,
    DESC,
    DENSE
)

_TopN =
IF ( 'Table'[_RankColumn] <= 3, [Type], "Others" )

 Result:

Please refer to the attachment below for details

For references:

RANKX  RANKX 

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@bleow , You can try measure like

RANKX(
table,
calculate(COUNT(table[Type])), ,
DESC,
Dense
)

if you are using above measure then this should also be measure

RETURN IF(
    Ranking<=3, 
    [Type], 
    "OTHERS"
)

or consider top N

TOPN: https://www.youtube.com/watch?v=QIVEFp-QiOk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42