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: 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 ...
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.

v-angzheng-msft's avatar
v-angzheng-msft
Icon for Community Support rankCommunity Support
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.