Forum Discussion
TopN + Others
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] ))))
Try this with measure:
TopN + Others = IF ([Ctry Rank] <= 3, MAX(Country[Country Name]), "Others")
3 Replies
Try this with measure:
TopN + Others = IF ([Ctry Rank] <= 3, MAX(Country[Country Name]), "Others")
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?
That worked... thank you!!!!
