Get Values and Counts in Table

[sheap069]

Hello,

I have a data set with Applications and Groups, and the data is related by the Group ID with the respective Application IDs. Application IDs have cities. 

Application City

1	Delhi
2	Delhi
3	Delhi
4	Delhi
5	Delhi
6	Delhi
7	Delhi
8	Delhi
9	Delhi
10	Delhi
11	Delhi
12	Mumbai
13	Mumbai
14	Mumbai
15	Mumbai
16	Mumbai
17	Mumbai
18	Mumbai
19	Mumbai

Application	Group
1	1
2	1
3	1
4	1
5	1
6	1
7	1
8	1
9	1
10	1
11	1
12	1
13	1
14	1
15	1
16	1
17	1
18	1
19	1

I want to display a table with one Group ID per row, with all the cities and their count in one single cell.

Group IDCity

1	Delhi (11), Mumbai(8)

I've attached sample data and the desired output in the file here. 

Thank you

[sheap069]

Hello @Vera_33 

As a follow up to this I have some Applications with a large number of cities. Is it possible to only display the three with the highest counts?

Thank you

[sheap069]

Hello @Vera_33 or anyone,

I would like to display just the top three cities that have the highest counts. Is this possible to do?

Another thing I would like to do is display the cities in order by the highest counts to the smallest.

Thank you

[Vera_33]

Hi @sheap069 

Sorry, missed your message...modify the T2 a little bit

Result = 
VAR T1 = GROUPBY(CityTable,CityTable[City],"COUNT",COUNTX(CURRENTGROUP(),[City]))
VAR T2 =TOPN(3, ADDCOLUMNS(T1,"TEST",[City]&" ("&[COUNT]&")"),[TEST],ASC)
RETURN
CONCATENATEX(T2,[TEST],", ")

[sheap069]

Hi @Vera_33 

That's okay. Thank you for the answer but it's still not ordering by the top count. For example, I have these counts for one Group ID

ADDR_CITY	Count of ADDR_CITY
LAGOS	15
PH	4
ABEOKUTA	1
BENIN	1
KADUNA	1
MBAISE	1
OGUN STATE	1
ONITSHA	1
PRETORIA	1
SOUTH AFRICA	1
UYO	1

But after applying your formula the top three showing are: KADUNA(1), BENIN(1), ABEOKUTA(1). I'm not sure how this order is generated when it should be Lagos(15), PH(4), ABEOKUTA(1).

Thanks again

[Vera_33]

Sorry @sheap069 

Made a mistake...but the order is not good...

Result = 
VAR T1 =TOPN(3, GROUPBY(CityTable,CityTable[City],"COUNT",COUNTX(CURRENTGROUP(),[City])),[COUNT],DESC)
VAR T2 = ADDCOLUMNS(T1,"TEST",[City]&" ("&[COUNT]&")")
RETURN
CONCATENATEX(T2,[TEST],", ")

[Vera_33 · Accepted solution]

Hi @sheap069 

You want a Table visual to display it? So it is a measure

Result = 
VAR T1 = GROUPBY(CityTable,CityTable[City],"COUNT",COUNTX(CURRENTGROUP(),[City]))
VAR T2 = ADDCOLUMNS(T1,"TEST",[City]&" ("&[COUNT]&")")
RETURN
CONCATENATEX(T2,[TEST],", ")

[sheap069]

Perfect, thank you!!