TopN + Others

[pshah]

I need to show Top(N) with Others... so say I want to see a total of 5 rows, but I want to show Top(3) and have the other 2 as "Others"... how would I go about doing that?

So my Top(3) are Japan, US, and Germany. In the TopN + Others column, I want to show the Country Names instead of the value. And then for the 2 that are blank (Hungary & Australia), I'd like to show "Others"

Here are the formulas for Total, Ctry Rank, and TopN + Others:

(1)  Total = CALCULATE(sum('Orders'[Payment]))

(2) Ctry Rank = RANKX(ALL('Country'[Country Name]), 'Country'[Total])

(3) TopN + Others = IF ([Ctry Rank] <= 3, [Total],
  IF(HASONEVALUE('Country'[Country Name]),
    IF(VALUES('Country'[Country Name]) = "Others",
       SUMX ( FILTER ( ALL ( 'Country'[Country Name] ), [Ctry Rank] > 3 ), [Total] ))))

[parry2k · Accepted solution]

Try this with measure:

TopN + Others = IF ([Ctry Rank] <= 3, MAX(Country[Country Name]), "Others")

[andrashodi]

Hi, 

How would it be possibile to visualize this categorizations on a column/pie chart? I replicated the solution and looks fine in a table, but I am not able to create a visual on it. For example, there are many bank accounts and I want to show top N as individual columns (say where the balance exceeds 1million) and the rest as "others" as one bar with their balances summarized.

Could you please help?

[pshah]

That worked... thank you!!!!