How to count duplicate counts of a string value and express it as TOP N and Others?

[Anonymous]

String value TOP N and Others

Hello teachers, I am a Korean student.

I want to count the duplicate values ​​of string data and express the TOP 5 and the remaining values.

I am able to count the total string values, but not able to count the duplicates of each value.

Below is a simple example.

Order	Duplicate Count		Produt	Rank	Total Count
A	3		A	1	3
B	2		B	2	2
B	2		C	3	2
A	3		Others		2
A	3
C	2
D	1
F	1
C	2

I plan to use these values ​​for matrix objects and map objects.

Please help me. Sir and Maam 

 

[Anonymous · Accepted solution]

Hi @Anonymous 

You can refer to the following example.

1.Create a calculated table

Table 2 = var a=SUMMARIZE('Table',[Order])
var b={"Others"}
return UNION(a,b)

2.Then create a measure

Measure = var a=SUMMARIZE('Table',[Order],"Countrows",COUNTROWS('Table'))
var b=TOPN(3,a,[Countrows],DESC)
var c=SUMMARIZE(b,'Table'[Order])
return SWITCH(TRUE(),SELECTEDVALUE('Table 2'[Order])="Others",SUMX(a,[Countrows])-SUMX(b,[Countrows]),SELECTEDVALUE('Table 2'[Order]) in c,SUMX(FILTER(a,[Order] in VALUES('Table 2'[Order])),[Countrows]))

Put the column of table 2 and the measure to a table visual

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.