Forum Discussion
Get Values and Counts in Table
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
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],", ")
7 Replies
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],", ")Perfect, thank you!!
Hello Anonymous
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
Hello Anonymous 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
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],", ")
