Forum Discussion

bleow's avatar
bleow
Frequent Visitor
5 years ago
Solved

RANKX: using aggregate functions on the expression

I have the following data on ID and Type:

IDTypeType TopNRanking
1AA1
2BB2
3COthers3
4AA1
5AA1
6BB2
7COthers3
8DOthers4
9EOthers5

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

Need Help
Show and Tell
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    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.

2 Replies