Power Pivot - Show top 3 values ang group rest.

[misqadv]

Hi,


Im looking for the way to show in pivot table top 3 max values (i.e. top 3 countries with biggest revenue) and group other countries in one cell. This thing will be used in monthly reports, so it will be good to make it atomatic. Is it possiable?
I will be very grateful for all your help.


BR,
Michal

 

[akice]

Not enough info to help. Need to know what inputs and desired output looks like. Or a sample file to download.

 

[misqadv]

Hi,
Ok I will post a sample file. This is workarund that I prepared. This is a only file I didn't implement a PowerPivot, All things goes to that can I prepare the same formula to count 3 biggest countries each month and group rest of them in one value. I've added a screen shot with pivot table source data, results and formulas used to get results --> https://ibb.co/kQfcrm .

Thanks for any help.

BR,
Michał

 

[misqadv]

Ok, problem solved. Maybe some one will use my formulas (all in calculation fields in manage mode):

1. Country Rank:=RANKX(ALL(Entities

);[Paid not on time amount])
2. Paid not on time amount:=SUM('Paid invoices'[Paid on time(2)])
3. Top3v2:=IF (    [Country Rank] <= 3;
    [Paid not on time amount];
    IF (
        HASONEVALUE ( Entities[Code] );
        IF (
            VALUES ( Entities[Code] ) = "Other";
            SUMX (
                TOPN (
                    COUNTROWS ( ALL ( Entities[Code] ) ) - 3;
                    ALL ( Entities[Code] );
                    [Paid not on time amount]; 1
                );
                [Paid not on time amount]
            )
        )
    )
)