Forum Discussion

sheap069's avatar
sheap069
Icon for Helper III rankHelper III
5 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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],", ")

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
sheap069
Icon for Helper III rankHelper III
5 years ago

Hi Anonymous 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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],", ")