Exclude a Specific Value from a Top N

[dinicholls]

Hi All,

I am trying to create a Top 10 debtors table, which I've done and works great! (Yay!!)

I now want to know if it is possible to exclude a certain code from this formula?

Here's my top 10 script

=if(Aggr(Rank(sum([>60days])),[Group No])<=10,[Group No],Null())

I now want to exclude 'GR-000000' and 'GR-A0000' from [Group No] in the pivot table.

I still need to see GR-000000 and GR-A0000 in my other pivot tables, just need to exclude it from the Top N pivot tables.

I've tried a few ways, but just think I'm confusing myself more than anything!

Thanks

Di

[MK_QSL · Accepted solution]

May be

Aggr(IF(Rank(SUM({<[Group No] -= { 'GR-000000','GR-A0000' }>}[>60days]),4)<=10, [Group No],Null()),[Group No])

or

=if(Aggr(Rank(sum({<[Group No] -= { 'GR-000000','GR-A0000' }>}[>60days])),[Group No])<=10,[Group No],Null())

[trdandamudi]

See if the below will work:

=if(Aggr(Rank(sum({<$[Group No]-={'GR-000000, 'GR-A0000'}>} [>60days])),[Group No])<=10,[Group No],Null())